Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Conditional formatting based on dates in other columns

0

Hello

I am trying to set up conditional formatting rules for the following scenario:

I have 3 date-formatted columns: Date A, Date B and Submission Date in columns A,B and C.

I want to format cells in column C according to the following rules:

  • Date B is less than 8 days AFTER Submission date - FILL RED
  • Date B is between 8 and 14 days AFTER Submission date - FILL AMBER
  • Date B is more than 14 days after Submission date - FILL GREEN
  • If Date B is blank apply the above rules to Date A

I have been able to come up with rules that work for the first three steps but have got stuck with applying to Date A if Date B is blank. Any help wpuld be greatly appreciated.

Simon

Answer
Discuss

Answers

0

For this purpose you might use the "Stop if True" checkbox.

  1. =IF(AND (B<>"", B<([SubDate]-8))
  2. Stop if True
  3. =IF(AND (B="", A<([SubDate]-8))

Looking in the Rules Manager, rules are implemented in the sequence in which they are listed. Accordingly, you should list all the B rules first, each marked "Stop if True", and followed by the A rules.

The alternative would be a single rule like this.

=IF(B = "", A<([SubDate]-8), B<([SubDate]-8))

The sequence in which the rules are applied remains important in either case.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login