Conditional formatting: Find text and highlight from other cellSolved/Closed

Question

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.

TrowaD · Answer

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

TrowaD · Answer

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 · Answer

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

Conditional formatting: Find text and highlight from other cell

3 responses

Similar discussions

Subscribe To Our Newsletter!