Find Columns By Name And Highlight Them Certain Colors Based On Text In Another Column

Jul 9, 2009

I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...

1. Vacation Type (will only have a text value of either "Cold" or "Warm")

2. Vacation Started (will always have a date *x/xx/xxxx)

3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)

4. Number of Days (currently has ALL truly blank cells)

THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE

Here's what I would like the macro to do...

Scenario 1 - for "Cold" values
Find "Cold" text values in the "Vacation Type" column
"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.

The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.

A) IF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.
OR..............................

View 12 Replies


ADVERTISEMENT

VBA Code To Find Dups, Highlight Them With Different Colors, Pretty Complicated

Aug 29, 2008

I'm trying to find some vba code that will find dups on my worksheet and highlight them in a special way: first dup = bright yellow, second dup = bright green, third dup = bright red, and if there is a forth, fifth or sixth dup, then just increment color number to next one, than next, etc...

I would like for the code to run as soon as there is a change on the worksheet (when I finish entering data in a row). I think I already have the right code to do that:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E9:E10000")) Is Nothing Then
Call dupSearch
End If
End Sub
I tested it out, seems to work as it should.

My major problem is that I need the code to check column F9:F10000 and compare the cells all along that column. That column concatenates 3 other cells in that same row (lastname&firstname&employee#), so if I run my dupSearch on that column only, I should find only real dups, and not just 2 people with the same name.

I also need to make sure it doesn't find empty cells, thinking they are dups. I would like to highlight column A to E of the row that contains the dup, and not just the cell in column F.

I have tried many other codes that I have found on the forum, but none of them work right with what I'm trying to do, and I just don't know enough VBA to make the changes I need.

View 9 Replies View Related

Column Fill Colors In A Column Chart When Some Columns Are Hidden

Jul 13, 2014

When I hide columns in a column chart the different fill colors I used on specific columns no longer show up with the colors I originally had. Is there a way to maintain the proper sequence of column fill colors even when some columns are hidden?

View 1 Replies View Related

How Do I Autofill Colors, Based On Input Of Text

Jun 16, 2008

i'm trying to do is have a formula that when certain text is typed into column 'b' (fmc, nmcmc, etc... anyways..) when an airplane is 'fmc' its 'green' and i want the aircrafts tail number block in column 'a' to autofill in with a green color, and when its 'nmcmc, i want it to be red. when its 'bq' i want it to autofill in as yellow.

View 10 Replies View Related

To Populate Three Columns With Text Based On Text Of Another Column

Dec 16, 2009

I am having a trouble in Excel sheet.My column A has a drop down list with text- possible, not possible, not required.Based on the text, i need to populate texts in columns B, C and D.

For example

Column A drop down selected is "possible"
then B coulmn should automatically populate "1-3"
C should populate with "3-5"
D should be "5-7"

I am using MS excel 2007.

View 9 Replies View Related

Background Color Of Cell Based On Colors In Column

Sep 28, 2013

I have a data base in which I classify cells in three colors (green, yellow, and red) on my own. I would like the first cell in each column to indicate if there is a red or yellow cell in it's column. Because red cells are more important then yellow cells, if there is a yellow and a red cell in the column, I would like the red to over ride the yellow and the first cell would indicate red.

So basically, if there is a red cell in the column, no matter what other colors there are, the first cell should show red. If there is only yellow or yellow and green cells, the first cell should show yellow. If there are only green cells or green cells and clear, the first cell should show clear.

View 1 Replies View Related

Create Step Colors Based On Column Of Numbers

Dec 6, 2006

I have to make a macheine cycle chart. I have a column containing Step Names and a column containing step time in whole seconds. I need to paste the step names across Row 1 and whole numbers counting to X down colum A. I then need to have each second represented by coloring the cell, for example, Step 1 is 1 sec. and Step 2 is 2 sec. if "Step 1" is in B1 I need B2 colored then for "Step 2" in C1 i need C3-4 highlighted and so on for a variable number of steps. This is my first question so I hope I was clear enough. With this much programed I will be able to make simple changes to tweak it to fit my needs.

View 2 Replies View Related

Changing Cell Colors For All Matching Textboxes Based On Text From Dropdown Menu

Feb 19, 2014

I have a large workbook, with multiple sheets. I would like to be able to select a name from a drop down menu, and have the cell color change for all entries of that name in my other sheets. I can do it manually, but it would be much cleaner and easier to have Excel do it for me. I have looked at other answers here on this forum and know that if it can be done,

View 1 Replies View Related

Find Specific Text In Multiple Rows / Columns And Align All Vertically To Same Column

Apr 25, 2013

I have a macro that converts all my PDF Purchase Orders to a text file and inserts the data/text horizontally into another document. However because the PDF's or the text within the PDF can be fomatted differently (that is on different lines etc) it therefore imports the information and it looks mis-aligned.I have attached a simple spreadsheet showing some sample text as it is imported and then below this how it should look like, all in line.

The range where the highlighted text in red is, is variable (but say nothing more than a variant of 10 columns). The text can also be Uppercase or Lowercase.So, I was wondering if there is macro code to find the "text" on various rows/columns and align it all in another column?

View 2 Replies View Related

Enter And Highlight Data In Columns Based On Other Columns

Jul 10, 2009

I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...

1. Vacation Type (will only have a text value of either "Cold" or "Warm")

2. Vacation Started (will always have a date *x/xx/xxxx)

3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)

4. Number of Days (currently has ALL truly blank cells)

THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE

Here's what I would like the macro to do...

Scenario 1 - for "Cold" values Find "Cold" text values in the "Vacation Type" column

"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.

The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.

Scenario 1-AIF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.

ORů
Scenario 1-BIF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY..................................

View 2 Replies View Related

Using Different Colors To Highlight Duplicate Values?

Sep 17, 2013

I have rows that contain property identifiers and their owners. One property ID can have multiple owners. I would like to color the rows differently to show each unique property ID with their 1 or more property owners. I have attached a file showing what I would like (with fake data). In excel, I was able to figure out how to highlight the duplicate values, but it only does them in one color. I would like each property ID value to have it's own color - as I show in the attached file. In the file, I have value 1234 as one color, the 4546 values as another color and 2233 values as another.

View 3 Replies View Related

Code To Highlight Duplicates With Colors

Jun 12, 2014

I am using below code to highlight duplicates with different colors.

I want to change the range in B3:C2000

[Code] .....

View 3 Replies View Related

Highlight Duplicated With Alternating Colors

Feb 21, 2014

I have a spreadsheet that contains thousands of rows and I need to highlight all the duplicates in Column B.

What I need though is one group o duplicates to be colored one color and then the next group of duplicates be colored a different color. I need just a 2 color banding.

My need for 2 colors is that the cells contain 9 digit numbers and there can be duplicate groups right next to each other and for the eye to distinguish a different grouping of cells I need that 2 color banding.

I have the below code that colors everything in one color. Can I be modified or a completely new code to do 2 colors?

Code:
Sub Dups()
Dim Rng As Range
Dim CL As Range

Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))

For Each CL In Rng
If WorksheetFunction.CountIf(Rng, CL.Value) > 1 Then
CL.Interior.ColorIndex = 6
End If
Next CL
End Sub

View 1 Replies View Related

Find And Highlight Text ...

Oct 10, 2007

Each cell in Range AL3..AL50000 have text (sentence - description of problem).

Cell AL1 has my search word (i.e. loose)

I would like to search range AL3..AL5000 for word "Loose" - if found highlight every cell where was found - Yellow.

If AL1 is empty - remove all highlights.

Here is what I have so far (patches of codes found in this forum)

Sub FindTextInCell()

Dim ws As Worksheet
Dim myText As String

myText = Sheets("detail_report").Range("AL1").Value

If myText = "" Then Exit Sub

Set Found = Range("AK2..AK56000").Find(What:=myText, LookIn:=xlValues, lookAt:=xlPart, MatchCase:=False)

If Not Found Is Nothing Then

Found.Interior.ColorIndex = 36

End If
End Sub

My code highlights only first found cell and if I type any other search word - highlight from the 1st search does not get removed.

View 9 Replies View Related

Finding Minimum Value In Column Based On Multiple Text Criteria In Other Columns?

Jan 2, 2013

This formula allows me to find the lowest value in column U where column N contains the text "NO".

{=MIN(IF($N$2:$N$10000="NO",$U$2:$U$10000))}

I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".

View 11 Replies View Related

Highlight Duplicates Based On 2 Columns

Oct 28, 2006

I have a column of information, and I want to find out of there are duplicates in it without alphabetizing it. Where there is a duplicate (say E2=E5), I want to check the adjoining column to see if the value of those fields are duplicates as well (does F2=F5). So, what formula would I put in D2 to see if the value of E2 has an equal anywhere in column E, and if any duplicates are found (say E10,E33, and E43), to check if F10,F33 and F43 equal F2 (the value adjoining the cell that was searched for).

to find duplicate entries in a database by using people's last names (in column E) and birthdays (in column F). Odds are if they have the same DOB and the same last name, we've found a duplicate.

View 9 Replies View Related

Highlight Duplicates Based On X Columns

Aug 28, 2007

I need to find a way to highlight duplicate records within a list. The records would first need to be compared by (1) the month (found in column A), (2) the account number (found in column B) and then (3) the commission type (found in column D).

I do not need the duplicates deleted just highlighted or better yet maybe have them identified as "Duplicates" in an empty column (such as column H).

View 9 Replies View Related

Highlight Rows That Share Text Across Columns

Feb 15, 2013

I am wondering how I can highlight rows that contain the same text across selected columns (not all). For example, consider the following table:

ID#6527
Jay
yellow
dog

[Code]...

I want to focus on Columns B, C, and D. I would like rows 3 and 5 to be highlighted, since they share the same text across the target columns. I assume this can be done via a formula in Conditional Formatting, but I'm not sure.

View 3 Replies View Related

Highlight Row If Cells In Two Adjacent Columns Contain Text Yes?

Mar 19, 2014

I would like to highlight the rows in my spreadsheet where columns G and H both contain the text "Yes". I've tried conditional formatting and VBA but can't seem to get either to work for what I need.

View 3 Replies View Related

Highlight Duplicate Values Based On 3 Columns?

Nov 8, 2012

I just want to colour duplicate values but want to do it with this Dictionary method

Code:
Sub highlight_Dups()
Dim cell As Range
Dim rng As Range
Dim dict As Dictionary

[Code]....

View 9 Replies View Related

Excel 2003 :: Highlight Row Based On Specified Columns?

Dec 19, 2012

I would like to have Excel 2003 be able to highlight the row if there is data in certain columns. Specifically if there is a number greater than zero.

For example the following could work:

[CODE][=OR(LEN(H9),LEN(I9),LEN(K9),LEN(Q9),LEN(R9),LEN(S9),LEN(W9),LEN(X9),LEN(Y9),LEN(AB9),LEN(AD9))/CODE]

However, there are times when I need to add a column/criteria. Thus I would have to go into conditional format and redo all the rows in the worksheet. Is there a smarter way to do this?

I was hoping to provide a list of cell address from the header or better than that look for specific numbers in the header row and if there is a number greater than zero, then highlight the entire row.

There is no specific pattern as to which columns would trigger the requirement.

Also how do you keep the ranges from moving when copying across and down. Sometimes I run into that issue. Maybe highlight the entire row and then enter the conditional format?

View 9 Replies View Related

How To Highlight Duplicates Based On Multiple Columns And Remove Them

Oct 8, 2011

I am trying to tell the spreadsheet that if column G *and* column V are both identical to another row's column G and V, then the entire row is a duplicate and to highlight and delete it. I also want to record a macro of this process to apply to all spreadsheets.

Here are the tutorials I am looking at, but whatever I am doing is not working the way it is working for them.

Conditional Formatting, find duplicates with multiple columns [URL]...

I am highlighting cell A2, and under Conditional Formatting, I select "Formula Is" =SUM(COUNTIF($G:$G,G2),COUNTIF($V:$V,G2))>1

Then I select the format as light orange text, but it is only highlighting A2....

That is as far as I have gotten, and I still do not know how to go about deleting the duplicate entries. I have also read there are issues with recording a macro when trying to find duplicates using more than one column in the formula, but I have not gotten that far so I can't test it.

As is likely evident in my formula and process, I am not very experienced with Excel.

View 2 Replies View Related

VBA Code To Find Row Number Based On Data Via Input Box And Highlight Cell?

Jun 21, 2014

I want to enter a unique ID into an input box which will tell which row that id is available and then it will again ask me which column the cell needs to be highlighted. Once i enter the data, it will then ask me what is the change in data (again via input box). i can then enter the change data and then it stops.

View 1 Replies View Related

Highlight Column Based On Value

Dec 7, 2008

I'm really struggling to achieve the following:

I have 75 rows and 10 columns populated with numbers (assume its A1 to J75 but it could be anywhere).

Firstly, if any of the numbers in a column are > 10 then I want to highlight all of the numbers in the column (even those

View 9 Replies View Related

Search Row By Column Value And Highlight Text - For Each Row

Jul 8, 2014

I've got a large Excel table that's full of names and e-mail addresses - it's a report of e-mails that merges some data from two systems we use; a registration form and a database. We want to clean up the data so it only lists folks who 'registered' (it's not always BOTH people listed - sometimes it's only one!) - and the best way to do that is to take the information from a specific column in each row, see if it exists elsewhere in the row and clear the information that doesn't match.

For example:
-A----------------B------------------C--------------------D--------------E------------------F-------------------G-
John ---------- Smith -------- js@email.com -------- Jane -------- Smith --------- js@email.com ------ John
Richard ------ McGee ---------j@email.com-----------Jim----------Samename ----jsn@email.com-------Rich
Mary-----------Ladyface ------ms@email.com--------Steve -------Smith ----------ss@email.com-------Steve

Ideally, for each row I'd like to search A and D for the string from column G. If it finds it, the cell and the two cells to the right are fine - but everything else is 'cleared' (not deleted).

So the above table would look like:
-A----------------B------------------C--------------------D--------------E------------------F-------------------G-
John ---------- Smith -------- js@email.com --------------------------------------------------------------- John
Richard ------ McGee ---------j@email.com-----------------------------------------------------------------Rich
-------------------------- -------------------------------Steve -------Smith ----------ss@email.com-------Steve

View 5 Replies View Related

Find Wont Find Existing Text When Columns/Rows Hidden

Oct 8, 2006

Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.

The error happens at this point: ...

View 3 Replies View Related

Highlight Row Range Based On Value In Another Column

Mar 13, 2008

I have been reading and searching on how to get around Excel's limit of 3(or 4) conditional formatting rules with VBA.

I have a pretty good grasp of what the code is telling the system to do, however what I am not understanding is how can I have the Code check multiple sets of columns?

ex Columns A,B and C Rows 1:100 need to be checked if they are between a particular range and clolums D,E, and F need to be checked against a completely different set of criteria.

I tried cutting and pasting the Sub, changing the Range values and the conditions, but it always errors out.

View 5 Replies View Related

Highlight Matching Cells Based On Manual Text?

Mar 9, 2013

I have a list of names in Column B (Starting at B5) with assignments to them in Column A. I want the people who receive the file, to enter their name in B1 exactly as it appears multiple times in sheet. And hope to use conditional formatting to highlight (change the back ground color) of each cell their name appears in.

I've used a number of formulas in the Conditional formatting including "=(ISNUMBER(MATCH($B5:$B100,$B$1,0)))", Countif's and "Not(isnumber)..." but can't find a formula that picks up the whole text.

View 3 Replies View Related

Highlight Rows After Specific Text In Column?

Dec 7, 2012

I have a 14 column report with a dynamic range of rows. In Column A, there will only be one cell that contains the text "Added Sections:".

I need to highlight, 10 whole rows up to the 14th column, after the cell that contains "Added Sections:".

View 6 Replies View Related

Excel 2010 :: Conditional Formatting - Highlight Values Based On 2 Columns Of Numerical Data

Apr 12, 2013

I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:

Column F:
6
6
14

Column L:
3
NA
17

I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.

Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved