Excel help - Rule and Conditional Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-1549164%22%20slang%3D%22en-US%22%3EExcel%20help%20-%20Rule%20and%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549164%22%20slang%3D%22en-US%22%3EI%20have%20a%20basic%20excel%20workbook%20with%20two%20sheets.%20Sheet%20%E2%80%9CA%E2%80%9D%20which%20is%20tracking%20all%20my%20%E2%80%9Ccases.%E2%80%9D%20This%20includes%20names%2C%20phone%20numbers%20and%20addresses.%20On%20sheet%20%E2%80%9CB%2C%E2%80%9D%20titled%20%E2%80%9Clarger%20sites%E2%80%9D%20I%20have%2040%20specific%20addresses%20that%20if%20there%20were%20to%20be%20entered%20into%20sheet%20%E2%80%9CA%2C%E2%80%9D%20I%20would%20like%20a%20formula%20to%20turn%20that%20column%20a%20color%20so%20it%20will%20stand%20out.%20I%20need%20help%20with%20knowing%20what%20that%20formula%20would%20look%20like.%20I%20know%20it%E2%80%99s%20conditioning%20formal%20and%20a%20rule%20with%20creating%20your%20formula...but%20what%20would%20lit%20looks%20like%20and%20how%20would%20I%20get%20column%20%E2%80%9CH%E2%80%9D%20(column%20for%20address)%20to%20cross%20reference%20sheet%20%E2%80%9CB%E2%80%9D%20if%20any%20of%20those%20addresses%20are%20inputted%3F%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20help%20would%20be%20WONDERFUL!!%20Thank%20you!!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1549164%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1549227%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20-%20Rule%20and%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549227%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F741768%22%20target%3D%22_blank%22%3E%40Psugirle%3C%2FA%3E%26nbsp%3B%2C%20something%20like%20the%20attached%3F%20Used%20the%20below%20formula%20in%20the%20conditional%20formatting%20rule.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS('All%20Cases'!%24H%242%3A%24H%2431%2CH2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1549236%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20-%20Rule%20and%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549236%22%20slang%3D%22en-US%22%3EThanks.%20I%20will%20attempt%20tomorrow%20and%20will%20let%20you%20know.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Does%20it%20matter%20if%20on%20Sheet%20%E2%80%9CA%E2%80%9D%20the%20address%20is%20in%20column%20H%2C%20but%20on%20sheet%20%E2%80%9CB%E2%80%9D%20all%20the%20addresses%20I%20want%20to%20compare%20too%20are%20in%20column%20%E2%80%9CA%3F%E2%80%9D%20Also%2C%20I%20have%2040%20addresses%20and%20I%20listed%20them%20individually%20on%20each%20row%20vertically.%20Will%20that%20change%20the%20formula%20you%20provided%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1549840%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20-%20Rule%20and%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549840%22%20slang%3D%22en-US%22%3E%3CP%3EI%20don't%20think%20that%20worked%2C%20or%20I%20just%20might%20not%20be%20doing%20it%20correctly.%20Here%20is%20my%20actual%20spreadsheet.%20I%20am%20looking%20for%20the%20rule%20that%20if%20any%20of%20the%20addresses%20listed%20on%20%22larger%20sites%22%20sheet%20is%20inputted%20on%20%22sheet1%22%20in%20column%20%22G%22%20that%20the%20row%20would%20change%20a%20different%20color%20so%20it%20would%20alert%20me.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F741768%22%20target%3D%22_blank%22%3E%40Psugirle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1549889%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20-%20Rule%20and%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549889%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F741768%22%20target%3D%22_blank%22%3E%40Psugirle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20isn't%20exactly%20what%20you%20requested.%20I%20just%20used%20the%20MATCH%20function%20plus%20conditional%20formatting%20to%20highlight%20the%20(new)%20cell%20adjacent%20to%20the%20street%20address%20when%20it%20matches%20one%20of%20the%20larger%20site%20street%20addresses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20it%20still%20highlights%2C%20but%20without%20intruding%20into%20the%20entire%20row.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1549912%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20-%20Rule%20and%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549912%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you.%20Sorry%2C%20I%20was%20trying%20my%20best%20to%20explain.%20So%2C%20I%20have%20to%20add%20another%20column%3F%20There%20is%20no%20way%20to%20make%20the%20%22WF%20Street%20Address%22%20just%20highlight%20the%20color%20to%20indicate%20large%20site%3F%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1549995%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20-%20Rule%20and%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549995%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F741768%22%20target%3D%22_blank%22%3E%40Psugirle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20explanation%20was%20perfectly%20clear.%20No%20need%20to%20apologize.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20no%20doubt%20is%20a%20way%20to%20do%20what%20you%20asked.%20Conditional%20formatting%20is%20tricky%20enough%2C%20in%20my%20experience%2C%20that%20if%20I%20can%20get%20anything%20to%20work%20when%20referring%20to%20comparisons%20between%20cells%20(or%20ranges)%2C%20I'm%20happy.%20I%20was%20not%20able%20to%20figure%20out%20(and%20continue%20to%20be%20unable%20to%20figure%20out)%20how%20to%20get%20the%20MATCH%20into%20the%20conditional%20formatting%20rule%20itself%20for%20the%20exact%20column%20containing%20the%20address.%20Whereas%20doing%20it%20in%20an%20adjacent%20column%20that%20contains%20the%20MATCH%20function%2C%20it%20was%20straightforward.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20be%20delighted%20to%20have%20somebody%20else%20show%20us%20both%20how%20to%20accomplish%20exactly%20what%20you're%20asking.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1552084%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20-%20Rule%20and%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552084%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F741768%22%20target%3D%22_blank%22%3E%40Psugirle%3C%2FA%3E%26nbsp%3B%2C%20Let%20me%20know%20if%20this%20works%20for%20you.%20I%20used%20the%20new%20XLOOKUP%20function.%20If%20you%20don't%20have%20XLOOKUP%2C%20it%20can%20work%20with%20other%20lookup%20functions.%3C%2FP%3E%3CP%3EBasically%20CF%20needs%20a%20TRUE%20or%20a%20FALSE%20so%20I%20always%20play%20around%20with%20the%20formulas%20on%20the%20worksheet%20and%20then%20paste%20into%20the%20CF%20dialog%20when%20everything%20works.%20I%26nbsp%3Bleft%20the%20calculations%20on%20the%20second%20sheet%20for%20you%20to%20review%20and%20change%20as%20you%20see%20fit.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1552796%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20-%20Rule%20and%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%20done!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20I%20like%20this%3A%20%3CEM%3EBasically%20CF%20needs%20a%20TRUE%20or%20a%20FALSE%20so%20I%20always%20play%20around%20with%20the%20formulas%20on%20the%20worksheet%20and%20then%20paste%20into%20the%20CF%20dialog%20when%20everything%20works.%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20next%20to%20impossible%20to%20do%20that%20playing%20around%20(especially%20for%20a%20situation%20like%20this%20one)%20IN%20the%20CF%20dialog%20box%20itself.%20So%2C%20again%2C%20well%20done!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
I have a basic excel workbook with two sheets. Sheet “A” which is tracking all my “cases.” This includes names, phone numbers and addresses. On sheet “B,” titled “larger sites” I have 40 specific addresses that if there were to be entered into sheet “A,” I would like a formula to turn that column a color so it will stand out. I need help with knowing what that formula would look like. I know it’s conditioning formal and a rule with creating your formula...but what would lit looks like and how would I get column “H” (column for address) to cross reference sheet “B” if any of those addresses are inputted?

Any help would be WONDERFUL!! Thank you!!
8 Replies
Highlighted

@Psugirle , something like the attached? Used the below formula in the conditional formatting rule.

 

=COUNTIFS('All Cases'!$H$2:$H$31,H2)

 

Highlighted
Thanks. I will attempt tomorrow and will let you know. Does it matter if on Sheet “A” the address is in column H, but on sheet “B” all the addresses I want to compare too are in column “A?” Also, I have 40 addresses and I listed them individually on each row vertically. Will that change the formula you provided?
Highlighted

I don't think that worked, or I just might not be doing it correctly. Here is my actual spreadsheet. I am looking for the rule that if any of the addresses listed on "larger sites" sheet is inputted on "sheet1" in column "G" that the row would change a different color so it would alert me.@Psugirle 

Highlighted

@Psugirle 

 

This isn't exactly what you requested. I just used the MATCH function plus conditional formatting to highlight the (new) cell adjacent to the street address when it matches one of the larger site street addresses.

 

So it still highlights, but without intruding into the entire row.

Highlighted

Thank you. Sorry, I was trying my best to explain. So, I have to add another column? There is no way to make the "WF Street Address" just highlight the color to indicate large site? @mathetes 

Highlighted

@Psugirle 

 

Your explanation was perfectly clear. No need to apologize.

 

There no doubt is a way to do what you asked. Conditional formatting is tricky enough, in my experience, that if I can get anything to work when referring to comparisons between cells (or ranges), I'm happy. I was not able to figure out (and continue to be unable to figure out) how to get the MATCH into the conditional formatting rule itself for the exact column containing the address. Whereas doing it in an adjacent column that contains the MATCH function, it was straightforward.

 

I'll be delighted to have somebody else show us both how to accomplish exactly what you're asking.

Highlighted

@Psugirle , Let me know if this works for you. I used the new XLOOKUP function. If you don't have XLOOKUP, it can work with other lookup functions.

Basically CF needs a TRUE or a FALSE so I always play around with the formulas on the worksheet and then paste into the CF dialog when everything works. I left the calculations on the second sheet for you to review and change as you see fit.

Highlighted

@TheAntony 

 

Well done!

 

And I like this: Basically CF needs a TRUE or a FALSE so I always play around with the formulas on the worksheet and then paste into the CF dialog when everything works. 

 

It's next to impossible to do that playing around (especially for a situation like this one) IN the CF dialog box itself. So, again, well done!