Conditional formatting: Find text and highlight from other cell [Solved/Closed]

drumroll13 4 Posts Tuesday July 14, 2015Registration date July 20, 2015 Last seen - Jul 14, 2015 at 11:43 AM - Latest reply: TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen
- Jul 23, 2015 at 11:12 AM
I have a table with names and number values alternating in a table. I add all the numerical values and highlight based on the total value for each name in a stoplight like fashion. I want to search the table for each name and then highlight the name the same as its corresponding value total. For example, John Doe's total value is yellow. I want every instance of John Doe in the table to be highlighted yellow. I have figured out how to get the highlight for my total value cell using 3 conditional formatting rules but I don't understand the search function let alone combining it with conditional formatting.
My table is currently 7 possible name columns by 67 rows but that could grow. Those names can be one of 32 possible names.
See more 

7 replies

Best answer
TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen - Jul 14, 2015 at 11:56 AM
1
Thank you
Hi Drumroll,

I don't understand your question.

I imagine your table to look like this:
Name 1	Name 2	Name 3	Name 4	Name 5	Name 6	Name 7
1	2	3	4	5	6	7
8	9	10	11	12	13	14
9	11	13	15	17	19	21

7 names in row 1, values in row 2 and 3 (in your case 67) and a total value row in row 4.

Now how can there be more instance of a single name?

Where am I taking a wrong turn?

Best regards,
Trowa

Thank you, TrowaD 1

Something to say? Add comment

CCM has helped 1670 users this month

drumroll13 4 Posts Tuesday July 14, 2015Registration date July 20, 2015 Last seen - Jul 14, 2015 at 12:06 PM
Oh sorry, so my table is like this
(sorry again i don't know how to make a table like you did)
staff hours staff hours
project 1 john 4 jill 2
project 2 greg 10 john 10
project 3 jill 3
etc.
so I have John twice and jill twice and jill should be green while john should be yellow for example
drumroll13 4 Posts Tuesday July 14, 2015Registration date July 20, 2015 Last seen - Jul 14, 2015 at 07:06 PM
I have figured out method to do this, unfortunately it requires 96 conditional formatting rules but I use the following formulas
=AND(H4=$A$4,$B$4<=120) highlight green
=AND(H4=$A$4,$B$4>120,$B$4<=136) highlight yellow
=AND(H4=$A$4,$B$4>136) highlight red
The A column has all the possible names and the B column has the associated total value. H4 in this case doesn't have the dollar signs as I plan to use format painter on all the other cells once I am done with the first cell. So as I go through I will be changing the A# and B# for each person as I make more rules.
If there is another easier or faster method of doing this, please let me know, I would greatly appreciate not inputting 93 more rules
TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen - Jul 16, 2015 at 11:21 AM
0
Thank you
Hi Drumroll,

Just for future reference, let me show you a clear way of showing a table:
First paste you table into your message, select the table, click on the arror-down button next to the <> symbol, click on the "c" as shown in the below pic:

Click on "Preview" to check how your message would look like if you click "Submit".

Unfortunately your query is still unclear to me.
You say Column A has all the possible names. If you give all the names a different colour, then I could wright a code for you to look up the names throughout the sheet to apply the same colour.
Is that what you are after?

Also consider uploading your file using a free file sharing site like www.speedyshare.com or ge.tt and then post back the download link. Careful with sensitive information. This might give better insight as to what you want to achieve.

Best regards,
Trowa
TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen - Jul 20, 2015 at 12:04 PM
0
Thank you
Hi Drumroll,

So for H4 on the Projects sheet, you want to check the hours per month on the Hour Calculations sheet and based on its value, change the colour to either green, yellow or red.

So with:
VLOOKUP($H4,'Hours Calculations'!$A$4:$B$36,2,FALSE)
we retrieve the hours per month value.

When this value is bigger then 136 you want to change the cell to red. So the first conditional formatting rule for H4 on the Projects sheet will be:
=VLOOKUP($H4,'Hours Calculations'!$A$4:$B$36,2,FALSE)>136 format cell as red.

Now do the same for the green and yellow colours.

Then drag H4 down as far as needed and click on the small square providing you with the option to "apply format only". This instead of applying format to each cell individually.

Does this yield the desired result?

Best regards,
Trowa
drumroll13 4 Posts Tuesday July 14, 2015Registration date July 20, 2015 Last seen - Jul 20, 2015 at 12:37 PM
Yes it does yield my desired result and works much cleaner than my approach. Thank you for your help Trowa. VLOOKUP does exactly what need.Just to confirm, for the yellow, it should look like this
=AND(VLOOKUP($H5,'Hours Calculations'!$A$4:$B$36,2,FALSE)>120,VLOOKUP($H5,'Hours Calculations'!$A$4:$B$36,2,FALSE)<=136)
because there are two requirements, greater than 120 and less than or equal to 136
TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen > drumroll13 4 Posts Tuesday July 14, 2015Registration date July 20, 2015 Last seen - Jul 23, 2015 at 11:12 AM
Hi Drumroll,

Great and you are right about the yellow, but you probably figured that out by trying.

Glad to be of help.

Best regards,
Trowa