CODEWITHSUNDEEP
excelvba
Steven
Steven

Reputation: 155

Apply formatting to a cell if its values don't match that of another cell (or group of cells)

Excel snapshot

I have an Excel document that is formatted similarly to the one pictured above. It is, however, very large. The ID and Flag fields are proprietary and will never change between SSN's.

I basically need to use ID to compare the 'OLD' records to the 'Source' records. If the column value of the 'OLD' record doesn't match any of the column values for the 'Source' record(s), fill that cell yellow.

I'm familiar with Access VBA but I've never written it in Excel and I'm not sure where to start, or if this is even possible. I've tried to include examples in my image.

You'll notice, for example, that Amy Winer's OLD SSN does not match up to either of her source SSNs, so that cell is yellow.

Where to start?

Upvotes: 1

Views: 137

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Select the column D from D2 downward, and enter this custom conditional formatting formula:

=COUNTIFS(A:A,A2,D:D,D2,F:F,"source")=0

and set the format/fill to yellow.

To apply the same rule to all column B through E, select the range B2:E7 and use the CF formula:

=COUNTIFS($A:$A,$A2,B:B,B2,$F:$F, "source")=0

Notice that we fix the columns A and F using $, and make the others movable

Upvotes: 1

Related Questions