Conditional formatting won't work - what am I doing wrong?

[sandwichgirl]

Hello

I am trying to conditionally format cells based on a date comparison between 2 other cells and I cannot get it to work using what I've found on on CF tutorials but am not sure what I am doing wrong.

I want to colour in I2 with green if I1 is less than E2, or colour it yellow if I2 is greater than G2 - I want to do this for each column using the date in the header relative to the cell in column E.

to look like this and I can show out of stock weeks by colour:

My only theory at the moment as to why it won't work is because the comparison cells are not on the same row but spent ages trying to figure it out so any help would be appreciated please?

Thank you

 

[RoryA]

What formula did you use? you need to be referring to $E2 to lock just the column and I$1 to lock just the row, so that each will adjust as for the relevant row/column.

 

[sandwichgirl]

Hi

I selected cell I2 and the CF formula was =I$1<=$E2 which should turn the cell green based on I2 saying 3/10 and E2 being 26/10.

I've played around a bit more and I think it is something to do with the format of I1 as I ran it in a another cell and it worked. The values in row I1 are a formula referencing to another sheet and are formatted as a date - if I overtype with the same date the CF works, visually the cell content looks the same but something is different. It appears the cell with the source date whilst looking like and formatted as a date isn't behaving like a date, so the CF won't work. Fun times.

I am going to see whether I can do something about the source data, so another problem altogether.

thanks for your help

 

[RoryA]

Try adding

+0

to the end of the formulas in row 1?

 

[sandwichgirl]

Try adding

+0

to the end of the formulas in row 1?

That formula is ='Call Off + FC Sheet'!BE8+0 and turned all my data to N/A. I went into the source and overtyped the date and it returned data.
The source is a system raised doc and no matter how I format those cells (£,% etc), they stay looking like a date - so looks like overtyping and autofill is what I'll need to do.

thanks

 

[RoryA]

Adding 0 should never produce a #N/A error in those cells