Tryin to get a 'Red Amber Green' colour scheme to appear within a certain range of a numbr

Basically I need a cell to turn Red Amber or Green if the number entered is within a certain range of a base number.

For example say the number is 43%
If the range between 40% & 46% is entered then it would turn Green
If the range is between 35% & 39% OR 47% & 51% then it would turn Amber
If the range is anything else then Red i.e. between 0% & 34% or 52% % 100%

There is probably an easier way to describe it than that but that's how envision it.

I tried playing with some conditional formatting but couldn't get it right

Any help much appreciated,
Ewen

Hello Ewen
If for example the cell you wanted to format was A1, formatted to Percentage, as your example, then the following Conditonal Formatting formulas should work:

For Amber:

Please Login or Register  to view this content.

For Green:

Please Login or Register  to view this content.

If all else is Red, then format the cell Red to begin with.

Hope this helps.
DBY

RHRP Planner.xlsx

Thanks for the reply I really appreciate it but I'm still struggling, proper noob sorry.

If I show you what I've got so far I wonder would that help.

I'm trying to create a spreadsheet for work that we can use to ensure we have the right split of hours in the right place, the ideal splits are provided to us & it up to us to put our staff in the right place.

The front page with "actual split" is where I'm trying to get the colours to change.

All figures are just for demo purposes at the moment

Thanks again,
Ewen

RHRP Planner.xlsx

Take a look at my attached reply. I've applied the formatting to cells B22:K22 on the RHRP sheet. Select the range and take a look at the Conditional Formatting> Manage Rules to see how it's applied.

Hope it does what you're looking for.

DBY

Hi again
Just thinking about the value ranges. What if your figures are between the ranges? For example 39.3%. With the current formatting this will turn the cell Red is this what you want? If not the formulas can be amended.

DBY

Yeah thats prity much what I have in mind, the only problem is when i put my sums back in b22, e22 etc.. which are =b14/o14, = e14/o14 etc.. then I'm not sure its working because the % I've setup is rounding up or down to the nearest. Also using 43 as our example, 47 turns red (sould be amber) but all other ranges work fine, strange.

Thanks,
Ewen

Yes. I should have thought of it initially. I've amended the formulas. Take a look at my 2nd attachment. See if this works better.

I think that's me up and running, I thank you very much for your help, would definitely never have got there on my own

Got to say I'm loving this forum.

Anyway thanks again & I'm sure I'm be back soon

Ewen

You're welcome. Glad you found the Forum helpful.

Regards
DBY