4

Let's say I have 2 columns that I'm comparing data in. If both cells match I want both cells to turn green. If they don't match I want them to turn yellow. Is there a formula that will allow me to check this for multiple records, or would this require looping in VBA? I can only seem to make this work 1 record at a time using conditional formatting. Thanks!

Edit: Adding results from provided answer

Improve this question

1 Answer 1

Reset to default
1

You can use conditional formatting, but you'll have to format each of the two columns separately.

Say your data is in A2:B100 (for simplicity)

Select the cells in columnA (A2:A100)
Select conditional Formatting >> New Rule >> Use a formula...
Enter the formula "=A3<>B3", choose a format and click OK

Repeat on column B.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

You can format both columns at once if you anchor the columns, like =$A3<>$B3.
@Tim Williams I'm getting some random results for some reason. I posted the results above. Column C and G are the columns im trying to match up, turning the cells in C that dont match G yellow. Any idea why some that match are turning yellow and some that don't are turning yellow?
@Chris: I have no idea why matches would be flagged, unless some of your values have added spaces or other such characters. Have you checked for that? If you don't care about spaces then you can always adjust your formula: =TRIM(A2)<>TRIM(B2)
@Tim I tested it on a test spreadsheet and still had the same problem. Maybe we're not trying to do the same thing. I get confused when I read the formula. What exactly is it doing? To me it reads if cell A2 does not equal cell B2 then highlight column A as yellow (or whatever I format it as), obviously it doesn't do that, could you explain what the formula is supposed to be doing? Maybe that will help.
Are you selecting the whole column before applying the formatting? You should only be selecting the cells containing the values you want to check. The formula just checks whether the value in A2 is not the same as the value in B2: adding TRIM() means it will ignore leading/trailing spaces in the values.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.