conditional formatting

[Prosched]

Hi guys,

i'm having an issue with conditional formatting, I need a conditional formatting formula that covers each day of the month with a different "quota".
one that's higher than = red and equal to = green.

now, i'm doing this by hand for every day of the month, but I wonder if theres a formula that covers it all by using a foruma once per month or something.

I have uploaded the file with the work i've done on a few months. if you've further questions please contact me as my explanation might be a little confusing.

thanks in advance!

[Pepe Le Mokko]

Could you please explain the logic? I don't understand what is to be formatted and the logic behind it ?

[Prosched]

Hi Pepe,

So the logic behind it, you've the month of January, every day theres a number off people that can take a day off.
for ex.
1 January theres 9 people that took off but there is only room for 8 people to take off, that means 9 should be collored RED as it exceeds the quota.
2 January theres 20 people that took off and theres room for 20 people, that means 20 should be colored GREEN as it is on quotum.

but instead of doing it for every day is there a way to do it for the entire month or even year with a formula.

[Pepe Le Mokko]

So only the rows with SCCM should be formatted?

[Prosched]

for now yes, there will be quota for CCM as well. But if I know how to format SCC then CCM would be the same

[GeoffW283]

The following 2 rules will cover the entire year.

First there has to be some consistency in the number of rows per month.
All months have 7 rows except March which has 6 rows
You need to add an extra blank row after March

Then clear (on a copy of your workbook!) your current numerous CF rules for the entire worksheet.

Create a new rule covering the range: =$A$1:$BM$40

Please Login or Register  to view this content.

Create a second new rule covering the range: =$A$1:$BM$40

Please Login or Register  to view this content.

Attached is an update to your workbook that implements the above, but note that I had to break your links to another workbook (Cabin 2021..xlsx) in order for me to test.

Hope this works for you, let us know.