My problem is that I want to color in red a list Custom Formatted Dates when the date is overdue.

For instance, I got two actions:

  • Buy Bread : Must be finished before 27/01
  • Buy Milk : Must be finished before 25/03

My Excel looks like:

       A                B                C
1   Action            Date            Finished
2   Buy Bread         27/01           NO
3   Buy Milk          25/03           Yes

I want to have the Column B be Red if the date is outdated and the action NOT finished

I've used Conditional Formatting such as:

  • Range is : "= $B:$B"
  • Formula is : "If value in the cell is > TODAY()
  • The cell must be RED only IF the action is marked as NO
  • And, of course, Formatting is with the Red Color.

I can't use the "IF A3 is NO" condition properly in my conditionnal formatting formula. How can I put this IF with the "CURRENT CELL"?

For exemple:


In your conditional formatting window simply enter this formula:


You may need to replace , with ; depending on your locality.

Note that conditional formatting works by checking a formula, and if the result is TRUE it applies the format. As a result you don't use IF statements, as these are used for applying a specified outcome if a condition is true.

An explanation of Conditional Formatting
The formula that you write is dynamic. Imagine you added a column at the end of your data, wrote the formula into the first cell and copied it down.

Because you didn't lock the row number, the formula changes to look at the row it is in. You may end up with 100 copies, each one looks at one of 100 rows.

This is the same, the range you selected is your column, and Excel modifies the conditional formatting to check each row separately. When it's checking row 6 the formula has changed to refer to B6.

This may be of help: chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics.

For future reference, modified for French: =ET($B2<=AUJOURDHUI();$C2="No")

  • Sorry, layout is false, I've corrected it. – Xaltar Jan 28 '14 at 10:28
  • Mh ... still not. I got "=ET($B:$B>AUJOURDHUI();$C:$C="No")" (i'm french). For range used : "=$B:$B". And it only makes my Excel laggy ... – Xaltar Jan 28 '14 at 10:47
  • Also, if you're using Excel 2010 (and don't need the workbook to work in 2003 or earlier) you should be using tables, and as such you can set the range for the formatting to the table column. It will then adjust when you add new rows to the table, which will reduce lag. As it is you're asking it to check around a million rows: office.microsoft.com/en-gb/excel-help/… – CLockeWork Jan 28 '14 at 11:08
  • Thanks ClockWork, this link is cool. I didn't know that. However, for the solution, I don't understand the $B2. This is only one cell right ? I need a whole range ... – Xaltar Jan 28 '14 at 11:56
  • Yes. But in your exemple, there is only a row. I need a range such as B2:B3 or B2:B10. – Xaltar Jan 28 '14 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.