Conditional Formatting not applying

**Lovelylou79** · 07-15-2013, 05:01 PM

Hi There,

I am having great difficulties with my conditional formatting. I have two columns that need to be compared - "C" is a static value while "E" is calculated by user input in another sheet.
I am using the condition =IF(E5<C5,1,0) ***E5 turns red if less than C5*** applied to "E5:E27", however C5 keeps changing to #REF! and it doesn't apply.
The sheet is protected so I tried to unprotect it in vba but it still wont apply. I also tried to write a macro to do the colour change but I have just made a mess of it.

Could someone please help. It is the very final part of a workbook due to go live by the end of the week.

This is what my messed up code looks like -

Please Login or Register  to view this content.

**protonLeah** · 07-15-2013, 06:13 PM

It looks like you are using the formula:

Please Login or Register  to view this content.

to write a 1 or 0 into the cell if E5 < C5. You cannot write values in a cell using conditional formatting. If you just want E5 red when less than C5, use:

Please Login or Register  to view this content.

. This will also turn E5 Red if it is empty and C5 isn't; so you can fix that with:

Please Login or Register  to view this content.

**Lovelylou79** · 07-15-2013, 06:44 PM

Thanks, That does work but when I save and close the workbook, then reopen it, the previously red cells are no longer red. I need this to be an automatic thing whenever I choose to view the sheets("Availability Report").
Why doesn't it work every time?

**protonLeah** · 07-15-2013, 11:01 PM

Did you put the formula in Conditional Formatting for the sheet or in a macro?
Is the sheet being updated automatically via formulas or macros?
What are the sheet protection options selected?