Condition formatting rule based on colour or number in another cell that already has a formula (without code)

[Woodpusher147]

HI all
I have a Rota sheet that needs to change cells in D9 and D16 to red if not enough staff, Yellow if minimum, Green if perfect, Blue if too many
This changes on different days.
I have done this by using a simple conditional formatting rule

Sunday below is a Black day so simply 6 however there are days that have 4 as perfect and 3 as min so this is the rule I have for those

I then have a formula which reads the staff cells D10-D15 & D17-D22 and counts the number of cells containing "in" =SUM(INDEX($O$7:$O$12,MATCH(D10:D15,$N$7:$N$12,0)))

At the moment, I have to manually add the conditional formatting to each days D9 and D16 cell.
My question is.
Is it possible to have the relevant conditional formatting rule be auto-populated depending on the colour or number of the DAY cell D7 without going into VBA code

My guess is no as this would need the formula in those cells to change but you guys know much more than me maybe an IF cell D7 is ? then use * formatting etc . ?? :/



Thanks for any help

 

[Woodpusher147]

Sorry about the above sheet screenshots dates, I changed the sunday to Monday to check it worked which as you can see it does turn green. this is what its like when proper dates are in

 

[Woodpusher147]

Can I just get a last bit of help with the above please

I haven't sorted this