Conditional formatting not working correctly

Question

I want past due dates to turn to a red filled cell. I've tried a variety of formulas but they always turn all the cells red. It's a new spreadsheet for 2020. I copied the information over from last year, cleared all formatting and conditional formatting and created new for this year. Any thoughts on why this isn't working correctly? 

Answer 1

Conditional Formatting Problem

Answer 2

Your CF had a lot of problems - duplications, broken zones, and not stopping when true. The linked file:

https://1drv.ms/x/s!AsKdy7Nfg\_Fbg0XXZ2-rwaMCI5Jd?e=HzDEmg 

has the CF for columns S to AB, rows 8 to 76 of sheet Amy fixed.

The general technique that you will need to use is to first decide on the block of cells that you want to have the same CF, then select all but the first row of that block and DELETE all the CF using "Clear rules" "Clear Rules From selected cells". Then select the first row of the block, fix the CF for that row, copy the first row, and then paste formats onto the rest of the block.  The formula that is used for the formula option references the first cell of the block when you apply it (for this, S8, since that is the first cell of the selection):

=S8<TODAY()

If is important to use "Stop when true" for any condition that looks at dates - if you have "<TODAY()" and "This month" as two conditions, the lower one will apply (This month) rather than the less than today if you don't check "Stop when true"

You also have CF applied to many, many, many rows where it is not needed. Select all the rows below your used area, and remove the CF, and all the columns to the right of your used area, and remove the CF from there.

Answer 3

Hi Kris,

Welcome to post back if you still need help.

Regards,

Clark

Answer 4

Thank you for looking at this document. My apologies for dropping from this conversation. I always appreciate the speed of help and help that this forum provides. I had other commitments I needed to do prior to a week vacation, was on vacation last week, and just now able to look at your reply and get this spreadsheet.

I'm not totally understanding your reply. I know I added extra rows to the CF because this spreadsheet has rows and columns added/deleted frequently. So to make it easier for me to follow once it does become quite large, I extended the cells being covered so I'll correct that. Other than that I'm not quite understanding how this all got broken. I know I have a LOT of CF's. 

To clarify, I use Format Painter to copy/paste CF's whenever a new column and/or row is added. Are you saying that I need to only Format Paint one cell and then highlight the cells (i.e., row or column) I need? And I shouldn't do the entirerow/column but only the cells needed?

Answer 5

How do I view the linked file please?