I have a spreadsheet where I document correspondence received, and I have a column "Days open" with a basic formula to work out how many days the case has been open for. We split the senders into two categories: important and unimportant (I have a hidden column titled "important" with yes or no in the cells). The unimportant ones are allowed up to 15 days to be closed, and the important ones up to 5.

How can I format the column of "Days open" to change colour after 5 days for the important letters and after 15 for the unimportant ones?

  • That's great. I'm now having the problem that when the case is Closed (marked in the "Days Open" column) the cell still has the same formatting as if it had been open too long. I want it to format a different way if it's closed. – Mish H Mar 27 '17 at 14:49


  • Column A is the important column
  • Column B is the days open column
  • Data starts from 2nd row

Then proceed as follows:

  • Select the range that needs conditional formatting. It is possible to choose more than one column i.e. you can make the entire row red if the condition is true.
  • Inside "Conditional Formatting" dialog choose "Use a formula..."
  • Enter the following formula:
    =IF($A2 = "yes", $B2 > 5, $B2 > 15)
  • Choose desired formatting
  • Done

The $ symbol in the formula allows you to copy the conditional formatting across multiple columns.

  • I've tried this and for some reason it is just formatting all cells with the formatting for the formula, regardless of whether the formula is true or not in the cell – Mish H Apr 26 '17 at 11:15

Use OR with AND as the formula in Conditional formatting.

This assumes A and B are you columns in respective order and you have,

Select Columns A and B, and use this rull to conditionally format the cells.:

  • How your post answers the question "How can I format ..."? – Máté Juhász Mar 21 '17 at 13:35

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.