SOLVED

Highlighting Cells with Lots of Conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-3280931%22%20slang%3D%22en-US%22%3EHighlighting%20Cells%20with%20Lots%20of%20Conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3280931%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20sheets%20together%20in%20one%20notebook.%20In%20sheet%201%20there%20are%20names%20randomly%20all%20around%20the%20sheet.%20Not%20any%20particular%20row%20or%20column%2C%20but%20I'm%20willing%20to%20do%20a%20formula%20or%20condition%20more%20than%20once%20if%20needed.%20In%20sheet%202%2C%20I%20have%20a%20table.%20In%20column%202%2C%20those%20same%20names%20are%20all%20in%20line.%20In%20column%205%2C%20each%20of%20those%20names%20are%20associated%20with%20a%20number.%20I%20want%20to%20find%20a%20way%20to%20highlight%20all%20of%20the%20names%20in%20sheet%201%20where%20the%20matching%20name%20in%20sheet%202's%20number%20is%20%3D%26gt%3B%2095.%20I%20know%20this%20is%20really%20confusing%20So%20ill%20try%20to%20make%20a%20thing%20below%20to%20visualize%20it%20better.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%201%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBob%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Jerry%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BKurt%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Larry%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%202%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBob%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20100.7%3C%2FP%3E%3CP%3EJerry%26nbsp%3B%20%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2087.4%3C%2FP%3E%3CP%3ELarry%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B96.3%3C%2FP%3E%3CP%3EKurt%26nbsp%3B%20%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B65.8%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20want%20Bob%20and%20Larry%20to%20Highlight%20on%20Sheet%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20if%20anyone%20can%20figure%20this%20out%20because%20I%20have%20been%20working%20so%20hard%20on%20this%20and%20cannot%20figure%20it%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3280931%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3281436%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20Cells%20with%20Lots%20of%20Conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281436%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1358781%22%20target%3D%22_blank%22%3E%40nannerb1115%3C%2FA%3E!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20second%20scenario%2C%20you%20may%20use%20the%20following%20formula%20to%20make%20a%20New%20Rule%20for%20Conditional%20Formatting...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DAND(A1%26lt%3B%26gt%3B%22%22%2CISNA(MATCH(A1%2CSheet2!%24B%3A%24B%2C0)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20with%20both%20the%20conditional%20formatting%20in%20place.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20takes%20care%20of%20your%20original%20question%2C%20please%20take%20a%20minute%20to%20accept%20the%20post%20with%20the%20proposed%20solution%20as%20a%20Best%20Response%20to%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3281360%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20Cells%20with%20Lots%20of%20Conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281360%22%20slang%3D%22en-US%22%3EI%20have%20one%20follow%20up%20question%20to%20this%20actually.%20Is%20there%20a%20way%20to%20create%20another%20formula%20to%20highlight%20a%20name%20on%20Sheet%201%20if%20it%20is%20not%20on%20the%20table%20on%20Sheet%202%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3281349%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20Cells%20with%20Lots%20of%20Conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281349%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much!%20You're%20a%20life%20saver%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3280986%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20Cells%20with%20Lots%20of%20Conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3280986%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1358781%22%20target%3D%22_blank%22%3E%40nannerb1115%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20data%20layout%20on%20Sheet2%20is%20as%20below...%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sheet2.jpg%22%20style%3D%22width%3A%20414px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F362637i4C4E32DE0BA6A915%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Sheet2.jpg%22%20alt%3D%22Sheet2.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20select%20cell%20A2%20on%20Sheet1%20and%20press%20Ctrl%2BShift%2BEnd%20to%20select%20the%20used%20range%20on%20Sheet1%20and%20make%20a%20New%20Rule%20for%20Conditional%20Formatting%20using%20the%20formula%20given%20below%20and%20set%20the%20format%20as%20per%20your%20choice.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DAND(A1%26lt%3B%26gt%3B%22%22%2CINDEX(Sheet2!%24E%3A%24E%2CMATCH(A1%2CSheet2!%24B%3A%24B%2C0))%26gt%3B%3D95)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20layout%20on%20Sheet2%20is%20different%2C%20tweak%20the%20above%20formula%20as%20required.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20with%20the%20conditional%20formatting%20in%20place.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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.