Conditional Formatting to compare cell's value against anothers

[Orlando322]

Longtime lurker who couldn't figure out/find a solution to my problem.

For simplification, I have values in column A from A8:A16, but I only want to format A13:A16. I have the cooresponding values in which I want to compare A13:A16 in nonadjacent rows and columns

I want to look at A13 and if it doesn't equal V42 (its cooresponding value), I want it to be highlighted yellow... if A14 doesnt equal V43, highlight yellow.

I know I can go to each cell and do that individually, but I am wondering if there is a dynamic formula I can use so that I dont have to go into each cell and hardcode it.

The current formula that I'm using, which highlights both cells where the cooresponding cells are equal and not equal is: =AND((A13<>V42),(A14<>V43),(A15<>V44),(A16<>V45))....I need it to just highlight the cooresponding cells in which they are not equal, not the whole range if only one is not equal.

Hope this makes sense and thanks in advance

 

[theBardd]

Select A13:A16 and use CF with a formula of =A13<>V42, then format as desired

 

[Snakehips]

Orlando332,

Welcome to MrExcel.

Highlight cells A13:A16

Then Home tab - Conditional formatting - New Rule - Use Formula to determine …..

Format values where this formula is true:

Enter =$A13<>$V42 and then set your format - OK - OK

That should do it.

 

[Orlando322]

Orlando332,

Welcome to MrExcel.

Highlight cells A13:A16

Then Home tab - Conditional formatting - New Rule - Use Formula to determine …..

Format values where this formula is true:

Enter =$A13<>$V42 and then set your format - OK - OK

That should do it.

Thanks for the quick responses.

It didn't seem to change anything. Maybe it's important to note that I am not doing the formatting for A14 (cooresponding value is unavailable), so A13 is compared with V42, A15 is compared with V43, and A16 is compared with V44. Not sure if that would change anything

 

[theBardd]

Just select A13,A15:A16 then.

 

[Orlando322]

Just select A13,A15:A16 then.

Hmmm, doing =$A13<>$V42 ends up highlighting all values in the range, which it shouldn't because some of the comparison values are equal. A13 is not equal to V42, so unless I'm wrong I believe that's why it is highlighting the whole range

 

[Snakehips]

Because of the disjoint of ranges in A you will need to apply two formulas.

Select A13 and apply $A13<>$V42

Select A15:A16 and apply $A15<>$V43

 

[Orlando322]

Because of the disjoint of ranges in A you will need to apply two formulas.

Select A13 and apply $A13<>$V42

Select A15:A16 and apply $A15<>$V43

That was one of the issues(that is now working), the other was that I needed to insert a round function so that they are exactly the same. Thank you so much!

 

[theBardd]

Hmmm, doing =$A13<>$V42 ends up highlighting all values in the range, which it shouldn't because some of the comparison values are equal. A13 is not equal to V42, so unless I'm wrong I believe that's why it is highlighting the whole range

Non-contiguous ranges work fine as long as you click-select them in the same operation, just a matter of doing it properly.

 

[Snakehips]

Non-contiguous ranges work fine as long as you click-select them in the same operation, just a matter of doing it properly.

Agree that non-contiguous ranges can be selected and have a common formula applied. Are you suggesting that a common formula could be applied here?