Several Conditional Formatting based on the Value of another Cell

[Excel_User_10k]

Hello All,

So basically there are several KPI's where the 'Target' Value can vary on a Month by Month basis. I would like to create several Conditional Formatting scenarios that highlights it as Green, Yellow, Red and Blue based on how close the ACTUAL figures are to said Target. At the moment I am having to change these ranges manually each Month if the Target changes. I have been able to do it for Green (i.e. when it =>"Target") but not the other three values.

To Illustrate:

Say the Target = 60%

The Cell showing the Current Actual Figure will have a Green background if it is =>60%
a Yellow background if it is between 50% - 59%
a Red background if it is between 30% -49% (Bare in mind that Red is actual default Cell Colour so doesn't technically need formatting but found it difficult to explain that)
and a Blue background if it is <30%

And then, say, the Target changed to 50%, I would like it to automatically reduce all of those Conditions by 10%. Is this possible, or am I asking too much?

 

[Z51]

If you put the target value in a cell, you could reference that in the conditional formatting formula. For example, if you put 0.6 into cell A1, your CF formulas could be something like:

=B1>=A1
=B1>=A1-0.1
=B1>=A1-0.3
=B1<A1-0.3

Make sure they're in the proper order in the CF formulas screen, too.

 

[Excel_User_10k]

Ok, so what does the B1 refer to? Would that be an example of the cell I am conditioning? Because whilst that would work, I would then need to create different Conditioning Formatting rules for every single Cell on the Sheet.

So the reference for the Target for Oct is Targets!L224, which is 50%. This is for "Max"

I have tried doing it as shown in the Screenshot. It accepts the rule, but as you can see, the value is 68% and above 50% and yet it isn't green. I don't understand why?