SOLVED

Highlighting Cells with Lots of Conditions

Copper Contributor

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 (Copper Contributor)
Solution

@nannerb1115 

 

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

Sheet2.jpg

 

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.

 

 

 

 

 

 

 

 

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

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?

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.

 

 

 

1 best response

Accepted Solutions
best response confirmed by nannerb1115 (Copper Contributor)
Solution

@nannerb1115 

 

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

Sheet2.jpg

 

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.

 

 

 

 

 

 

 

 

View solution in original post