Conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2499684%22%20slang%3D%22en-US%22%3EConditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2499684%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20worksheets%20and%20I%20am%20trying%20to%20work%20out%20if%20there%20is%20a%20way%20to%20say%20that%20if%20a%20number%20is%20worksheet%20A%20is%20also%20in%20worksheet%20B%20-%20can%20it%20be%20highlighted%20green%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2499684%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2500367%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2500367%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1091536%22%20target%3D%22_blank%22%3E%40bianca2089%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20define%20a%20named%20formula%20'%3CSTRONG%3Eformat%3F%3C%2FSTRONG%3E'%20that%20refers%20to%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20COUNTIFS(LookupRange%2C%20currentCell)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewhere%20%3CSTRONG%3ELookupRange%3C%2FSTRONG%3E%20is%20a%20range%20on%20the%20other%20sheet%20and%20%3CSTRONG%3EcurrentCell%3C%2FSTRONG%3E%20is%20a%20relative%20reference%20to%20the%20active%20cell.%26nbsp%3B%20Use%20on%20the%20worksheet%20will%20generate%20circular%20references%20but%20within%20conditional%20formats%20it%20is%20OK.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20308px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292493i03F03FDEC2ED75FD%2Fimage-dimensions%2F308x102%3Fv%3Dv2%22%20width%3D%22308%22%20height%3D%22102%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have two worksheets and I am trying to work out if there is a way to say that if a number is worksheet A is also in worksheet B - can it be highlighted green?

1 Reply

@bianca2089 

You could define a named formula 'format?' that refers to

= COUNTIFS(LookupRange, currentCell)

where LookupRange is a range on the other sheet and currentCell is a relative reference to the active cell.  Use on the worksheet will generate circular references but within conditional formats it is OK.

 

image.png