Forum Discussion
Conditional Formatting with Multiple Date critiera
I am trying to develop an excel that highlights cells based on: start date, due date, and completion date. Short of creating a new conditional formatting rule for every single row, is there a way to format the rule so that it only applies to the corresponding start date in the same row, and apply that same rule to multiple rows?
For example:
Column A: Start Date
Column B: Task 1, date completed input
Column C: Task 2 date completed entered
Highlight for values between Start Date and Start Date + 365
Appreciate the help!
Hi,
Yes! You can apply one conditional formatting rule to multiple rows dynamically using a relative reference in your formula.
Select the entire range where you want the formatting to apply.
Example: If your data starts in row 2 and goes down to row 100, select B2:C100 (assuming columns B and C contain task completion dates).
Go to Conditional Formatting → New Rule.
Select "Use a formula to determine which cells to format".
Enter the formula for Column B:=AND(B2>=$A2, B2<=$A2+365)
This checks if the value in column B falls between the Start Date (A2) and A2 + 365 days.
5. Click "Format" → Choose a highlight color and press OK.Repeat for Column C (Task 2), using this formula:=AND(C2>=$A2, C2<=$A2+365)
6. Click OK to apply the rule.Now, the rule will apply to all rows dynamically without needing individual rules per row.
Hope this helps! Let me know if you need any adjustments. 😊
Let's say the dates are in B2:C100.
Select this range; B2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=AND(B2>=$A2, B2<=$A2+365)
Click Format...
Activate the Fill tab.
Select a fill color.
Click OK, then click OK again.
