SOLVED

Highlight Duplicates (Extended) - 2 Columns (shade both columns)

%3CLINGO-SUB%20id%3D%22lingo-sub-3043914%22%20slang%3D%22en-US%22%3EHighlight%20Duplicates%20(Extended)%20-%202%20Columns%20(shade%20both%20columns)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3043914%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20conditional%20formatting%20with%20a%20countif%20formula%3C%2FP%3E%3CP%3E%3DCOUNTIFS(%24B%242%3A%24B%247%2CB2%2C%24A%242%3A%24A%247%2CA2)%26gt%3B1%3C%2FP%3E%3CP%3Eit%20correctly%20highlights%20but%20I%20want%20to%20extend%20the%20highlight%20to%20column%20B%20as%20well.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20currently%20highlights%20column%20A%20only.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20do%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Tony2021_0-1640119215784.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F335470i2C80FB3ACD40AD54%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Tony2021_0-1640119215784.png%22%20alt%3D%22Tony2021_0-1640119215784.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3043914%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3044035%22%20slang%3D%22en-US%22%3ERe%3A%20Highlight%20Duplicates%20(Extended)%20-%202%20Columns%20(shade%20both%20columns)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3044035%22%20slang%3D%22en-US%22%3EPerfect.%20I%20see%20there%20was%20a%20small%20change.%20the%20%24.%20thanks!%20Works%20great.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3043950%22%20slang%3D%22en-US%22%3ERe%3A%20Highlight%20Duplicates%20(Extended)%20-%202%20Columns%20(shade%20both%20columns)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3043950%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20A2%3AB7.%20The%20active%20cell%20should%20be%20in%20row%201.%3C%2FP%3E%0A%3CP%3ECreate%20the%20conditional%20formatting%20rule%20with%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS(%24B%242%3A%24B%247%2C%24B2%2C%24A%242%3A%24A%247%2C%24A2)%26gt%3B1%3C%2FP%3E%3C%2FLINGO-BODY%3E
Super Contributor

hello, 

I have a conditional formatting with a countif formula

=COUNTIFS($B$2:$B$7,B2,$A$2:$A$7,A2)>1

it correctly highlights but I want to extend the highlight to column B as well.  

It currently highlights column A only. 

 

How can I do this? 

Please see attached file. 

Tony2021_0-1640119215784.png

 

2 Replies
best response confirmed by Tony2021 (Super Contributor)
Solution

@Tony2021 

Select A2:B7. The active cell should be in row 1.

Create the conditional formatting rule with formula

 

=COUNTIFS($B$2:$B$7,$B2,$A$2:$A$7,$A2)>1

Perfect. I see there was a small change. the $. thanks! Works great.