I want to highlight cells based on a set of values that is made from a formula. I have the data in the sheet from A2:D10 (can be text, numbers, anything). I have compared this data with another sheet using formulas and have the text as same and different in cells E2:H10.

Now I need to highlight the data available in cells A2:D10 based on the values in E2:H10. For example if the data in cell G3 is Different, I want it to highlight the cell C3 as 'red' and cell cell E2 is same, I want it to highlight the cell A2 as 'Green'.

I tried to accomplish this with conditional formatting, but it doesn't seem to work properly. Any ideas? Can we accomplish this with conditional formatting or with VBA?


Antony's answer had the right idea, but misunderstood the question slightly. Here are the formulas modified for your use case.

In Conditional Formatting, you'll want to use the following:

For Green/Same: =IF(LOWER(E2)="same",1,0)
For Red/Different: =IF(LOWER(E2)="different",1,0)

enter image description here

Note that I added the function LOWER to these formulas. You may not need that, but I prefer to normalize input when I don't know where it's coming from. That way you can match same, Same and SAME equally.

  • Now it works, Good learning! Thanks for the help. – Rose Feb 12 at 6:59

Using conditional formatting, you want the expression:

[For Green/Same] =IF(A2=E2,1,0)

[For Red/Different] =IF(A2<>E2,1,0)

Apply it to your range of data (drag and copy formatting) and done!

Test Data

  • 2
    Hi, thanks for the answer. My cell A2 and E2 does not have the same values. E2 have the cell display value of text 'Same' and A2 have some other texts. I have texts of 'Same' and 'different' in cells E2:H10(Actually cells in E2:H10 have the formula that displays the text 'Same' and 'Different'), but there are other texts available in cells A2:D10. I wanted to highlight the cells in A2:D10 based on the cell contents in E2:H10. Like if cell display text of E2 is 'Same' I wanted to highlight the cell A2 as Green &if E3 have text ' Different' I wanted to highlight cell A3 as Red and so on. Thanks – Rose Feb 11 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.