Conditional formatting-

[srizki]

I have the following formula in conditional formatting. This formula is working correctly in October column (column H) but highlight only on cell H2. What do I need to change so that formula work for column H,(October).

=AND(MONTH(F1)=MONTH(TODAY())-1,F2="")

F1 is August, G1=Sep. H1=Oct. I1=Nov. J1=Dec.
The conditional formatting is on F2:J48



Please advise.

 

[NARAYANK991]

Hi ,

Try changing the relative reference F1 to F$1 , so that as the formula is applied to rows 3 and downward , the reference to F1 remains the same.

Without the absolute $ sign , the formula will become F2 , F3 , F4 ,...

However , I don't understand what you mean by saying F1 contains August , since within the CF formula MONTH(F1) will return an error value.

F1 should contain a date though the cell might be formatted to show only the month.

 

[Joe4]

Can you explain exactly what this is supposed to be doing?
Are you always wanting to look at row 1 (i.e. F1), or is it always looking at the row above?
Are the values in Row 1 actual dates formatted like "mmm", or are they text?

 

[srizki]

The row 1 has months, and dates are formatted as d-mmm. so the formula checks the date in the column (heading) and if the month is prior month, October in this case, it highlight the cell, only if the cell is empty.
it is working correctly on first row, that is row 2. and I tried to check the validity of the formula by changing the date and it worked but only on top row.

 

[srizki]

Narayuank,
Yes it worked when I changed it.
Thanks,
I had it correct on another sheet and I forgot here.
Thanks,