Conditional Formatting Rule Not Working on One Cell

0
votes

I have a perplexing problem with one of my Excel spreadsheets. I have a "task list" spreadsheet, with conditional formatting to highlight items that are near their due date and overdue. One cell in my entire worksheet is not allowing the conditional formatting, and I am not sure why. It does not matter what is in the cell.

I have tried:

  • deleting the table row
  • deleting the entire row
  • re-typing the cell
  • re-sorting the cells
  • deleting and re-creating the rule
  • verifying that the rule is applied to that cell

Any ideas as to what is causing this?

The affected cell is D5. You can download the spreadsheet here: Google Drive download link

enter image description here

1
excelformattingconditionalexcel-2016
Yes I have done that. I switched to a different format and switched back. I also used format painter from another cell to that one. - Jeff Miller
Does the cell change to a serial number if you change the format to Number or General? - urdearboy
@urdearboy yes it does - Jeff Miller
hm. Well my last question is does this cell actually fit the conditional format criteria? - urdearboy
@urdearboy I have added a link to the file if you are interested in downloading it and trying a few things. Any help would be appreciated. - Jeff Miller

1 Answers

1
votes

Since that's cell D5, instead of ROW()>5 you should use ROW()>=5.

Also "before today" would be <TODAY() . The +1 makes it "before tomorrow".

It's kind of unusual to have a row number in there at all. I assume you did that so you could apply the format to the whole column but if the extra cells are not dates (like, are just titles) then they shouldn't be affected by the conditional formatting.

If it was me, I would clear all the formatting from that column and in D5 add the simple conditional formatting criteria =D5<TODAY(), then copy that cell, click heading D to select the entire column, Ctrl+Alt+V and Paste Formats.

Better yet, only apply the Conditional Formatting to the cells that need it, not the whole column. Conditional Formatting increases the file size and slow down calculation more that one would think. (I was argued that but was proven wrong!)