SOLVED

# Highlighting Cells with Lots of Conditions

Occasional Contributor

# Highlighting Cells with Lots of Conditions

I have two sheets together in one notebook. In sheet 1 there are names randomly all around the sheet. Not any particular row or column, but I'm willing to do a formula or condition more than once if needed. In sheet 2, I have a table. In column 2, those same names are all in line. In column 5, each of those names are associated with a number. I want to find a way to highlight all of the names in sheet 1 where the matching name in sheet 2's number is => 95. I know this is really confusing So ill try to make a thing below to visualize it better.

Sheet 1:

Bob                                                                  Jerry

Kurt

Larry

Sheet 2:

Bob     |      100.7

Jerry    |      87.4

Larry   |       96.3

Kurt    |       65.8

I would want Bob and Larry to Highlight on Sheet 1.

Thank you if anyone can figure this out because I have been working so hard on this and cannot figure it out.

4 Replies
best response confirmed by nannerb1115 (Occasional Contributor)
Solution

# Re: Highlighting Cells with Lots of Conditions

If the data layout on Sheet2 is as below...

Then select cell A2 on Sheet1 and press Ctrl+Shift+End to select the used range on Sheet1 and make a New Rule for Conditional Formatting using the formula given below and set the format as per your choice.

``=AND(A1<>"",INDEX(Sheet2!\$E:\$E,MATCH(A1,Sheet2!\$B:\$B,0))>=95)``

If the layout on Sheet2 is different, tweak the above formula as required.

Please find the attached with the conditional formatting in place.

# Re: Highlighting Cells with Lots of Conditions

@Subodh_Tiwari_sktneer Thank you so much! You're a life saver

# Re: Highlighting Cells with Lots of Conditions

I have one follow up question to this actually. Is there a way to create another formula to highlight a name on Sheet 1 if it is not on the table on Sheet 2?

# Re: Highlighting Cells with Lots of Conditions

You're welcome @nannerb1115!

For the second scenario, you may use the following formula to make a New Rule for Conditional Formatting...

``=AND(A1<>"",ISNA(MATCH(A1,Sheet2!\$B:\$B,0)))``

Please find the attached with both the conditional formatting in place.

If that takes care of your original question, please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.