Forum Discussion
Conditional Formatting Based on Frequency
Hi all,
Just wondering if it's possible to highlight a row using conditional formatting based on one column displaying the frequency of a particular entry? Example screenshot below!
What I am trying to do is apply the following rules:
- Where the task is monthly
- And was last completed less than 30 days ago = highlight row green
- And was last completed 31-40 days ago = highlight row orange
- And was last completed over 41 days ago = highlight row red
- Where the task is quarterly
- And was last completed less than 90 days ago = highlight row green
- And was last completed 90-100 days ago = highlight row orange
- And was last completed over 101 days ago = highlight row red
- Where the task is annual
- And was last completed less than 365 days ago = highlight row green
- And was last completed over 364 days ago = highlight row red
I think I will have to use the TODAY function, but I'm not sure if it's possible to get excel to read the frequency column as well, as it would need to apply a variety of rules dependent on this particular column; is this possible, please?
Thanks 🙂
Select A2:C6 (or however far down the data go).
The active cell in the selection should be in row 2.
On the home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula =AND($B2<>"",$C2<>"")
Click Format...
Activate the Fill tab.
Select red.
Click OK, then click OK again.
Repeat the above steps, but with the formula
=$C2>=TODAY()-IF($B2="Monthly",40,IF($B2="Quarterly",100,365))
and orange.
Finally, repeat them again with the formula
=$C2>=TODAY()-IF($B2="Monthly",30,IF($B2="Quarterly",90,365))
and green.
2 Replies
Select A2:C6 (or however far down the data go).
The active cell in the selection should be in row 2.
On the home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula =AND($B2<>"",$C2<>"")
Click Format...
Activate the Fill tab.
Select red.
Click OK, then click OK again.
Repeat the above steps, but with the formula
=$C2>=TODAY()-IF($B2="Monthly",40,IF($B2="Quarterly",100,365))
and orange.
Finally, repeat them again with the formula
=$C2>=TODAY()-IF($B2="Monthly",30,IF($B2="Quarterly",90,365))
and green.
- Thank you so much! Not as complicated as I thought...
