Home

Conditional formatting equation difficulty

%3CLINGO-SUB%20id%3D%22lingo-sub-180307%22%20slang%3D%22en-US%22%3EConditional%20formatting%20equation%20difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180307%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20create%20a%20formula%20determining%20which%20cells%20to%20format%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet1ColumnA%20is%20a%20series%20of%20names.%3C%2FP%3E%3CP%3ESheet2ColumnA%20has%20more%20names%2C%20some%20which%20match%20Sheet1ColumnA.%3C%2FP%3E%3CP%3ESheet2ColumnA%20also%20has%20duplicates%20which%20have%20differing%20addresses%20(ColumnE).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20highlight%20(fill%20cell%20w%2F%20a%20color)%20rows%20in%20Sheet1ColumnA%20which%20match%20duplicate%20rows%20in%20Sheet2ColumnA%20%26amp%3B%20then%20apply%20the%20formula%20as%20a%20macro%20to%20be%20used%20monthly.%20I%20believe%20I%20can%20do%20this%20if%20I%20use%20a%20new%20Conditional%20Formatting%20rule%20with%20the%20proper%20formula%20encapsulating%20the%20conditions%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20makes%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20way%20I've%20tried%20to%20explain%20it%20to%20myself%20is%20IF%20Sheet1ColumnA%20matches%20Sheet2ColumnA%20AND%20Sheet2ColumnA%20%3D%20Duplicates%20THEN%20format%20as%20so.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-180307%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-180400%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20equation%20difficulty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180400%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Shayon%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20Conditional%20formatting%20rule%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTIF(Sheet2!%24A%3A%24A%2C%24A1)%26gt%3B1%3C%2FPRE%3E%0A%3CP%3Eon%20your%20column%20A%20in%20Sheet1%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Shayon Lashgari
Occasional Visitor

Hello,

 

I'd like to create a formula determining which cells to format:

 

Sheet1ColumnA is a series of names.

Sheet2ColumnA has more names, some which match Sheet1ColumnA.

Sheet2ColumnA also has duplicates which have differing addresses (ColumnE).

 

I want to highlight (fill cell w/ a color) rows in Sheet1ColumnA which match duplicate rows in Sheet2ColumnA & then apply the formula as a macro to be used monthly. I believe I can do this if I use a new Conditional Formatting rule with the proper formula encapsulating the conditions above.

 

I hope that makes sense.

 

Another way I've tried to explain it to myself is IF Sheet1ColumnA matches Sheet2ColumnA AND Sheet2ColumnA = Duplicates THEN format as so.

 

Thank you

1 Reply

Hi Shayon,

 

You may use Conditional formatting rule

=COUNTIF(Sheet2!$A:$A,$A1)>1

on your column A in Sheet1 

 

Related Conversations
conditional formatting with formula
kdwork in Excel on
3 Replies
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Format Date in header and footer
Joe Cangelosi in Excel on
3 Replies
Date Formatting Won't Change
Steve Gould in Excel on
10 Replies
Conditional Formatting values across tables
ChrisJP in Excel on
5 Replies