Conditional Formatting - Not working in odd cells where it should be???

Question

Hey,

I have a sheet where I'd applied the same rule to different blocks of numbers in the same column (I can't remember why I did it this way, all I can think is that because I'm not very good with conditional formats I did them all separately).

Now I've tried to use a single rule to cover them all to make it a neater sheet.  However, for some reason the rule isn't working for odd cells where is should / has in the past using the old multiple series of rules.  I can't figure out why.

The rule I'm using is: =AND(I5=W5,I5<>"") highlight green, see screenshot.

I've applied a screenshot below of where the error is occurring.  It's also happening in 1 cell above, which is in the middle of cells that are highlighted correctly.

I added the condition by highlighting the entire column W and entering the row number for the first cell that I want the rule to apply to (row 5).

Any ideas what I'm doing wrong?

Cheers.

Answer 1

Hi,

My guess would be that in the rows that aren't getting formatted to values aren't the same as they appear in the cell. Try this for a pair of cells you think should be formatted.

=I20=W20

That has to return TRUE for the cell to be formatted and if it returns FALSE then the values aren't the same. You could try this CF formula.

=AND(ROUND(I5,2)=ROUND(W5,2),I5<>"")

Answer 2

Thanks,

I tried the ROUND formula and it still doesn't work (although an extra cell with 8 in it (shown on the screenshot above is now highlighted when it wasn't last time))!!!

I've attached a new screenshot (below) of the new result.  I've even increased the decimal points for 1 of the cells that is incorrect to show that 2dp should work fine.

I've even tried copy and pasting the '8.0000' from the right hand side directly into the left hand side cell and it still doesn't highlight the cell green.

It's a strange one!

The '=I84=W84' rule does return a correctly highlighted cell!!!

Answer 3

Hi,

Can you upload a redacted copy of the workbook to your Onedrive and post the link here?

Answer 4

First remove your conditional formatting from entire column. 

Now select entire column W while your active cell is W1 now in conditional formatting give this formula by changing 5 to 1,  and apply it.  I am hopeful, it will work.

=AND(I1=W1,I1<>"")

Answer 5

Thanks,

I've never used the Onedrive before.  I hope this works.  Please let me know if you get access to anything you shouldn't and I'll delete this link straight away.

https://onedrive.live.com/redir?resid=8112052B896CBD6!161&authkey=!ANQMqjkS6CVOdgo&ithint=file%2c.xlsx