Excel 2010 Graded 3-Color Scale Conditional Formatting

[PWD]

Good afternoon, we've been requested to supply Colour-scaled Conditional Formatting for one of our departments whose data shows in minutes. They want to be able to show when these minutes are within +- 30 minutes as green, graduating out to red at +- 90 minutes.

I've put together just a few test rows ranging from -9 to 9 and I have managed to create graded colouring for either the positive OR the negative by having two separate rules but each one will only apply if it's the first in the list of rules. Then the rest of the numbers (either +ve or -ve according to the unsuccessful rule) all become filled with green.

Any ideas?

Many thanks,
D€$

 

[SkipVought]

HI,

Use ABS() for +/- one rule.

What formulas are you using?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

You might use something like this...
[tt]
=ABS(A2-Base)>TIME(0,30,0)
[/tt]

What do you want:

GREEN for what range of minutes? 0-30?
YELLOW for what range of minutes? 30-60?
RED for what range of minutes? 60-90?
What happens over 90?


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's a sample.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Thanks Skip, I'll have a look in the morning!

Many thanks,
D€$

 

[PWD]

Hi Skip, what our people are after is a way of formatting existing data to show how far away from 0 each row is regardless of whether the figure is negative or positive. The attached workbook just has a few rows of each. What they tell me they want is anything plus or minus 30 is green; anything from that plus or minus 30 to plus or minus 45 should be amber and anything further away - up to 90 - should be red.

So, it doesn't matter which way it goes from 0, they want it graded from green to red.

The attached has two formatting rules that appear to do the job but only the top one is applied and the rest of the rows are green.

Many thanks,
D€$

 

[SkipVought]

Your negative value formula has problems as you can see in your workbook that I uploaded. If you take any negative value like -80 and make it 80, the color should not change. BUT IT DOES IN EVERY CASE.

I tried using a relative reference =ABS(E2) in the 3-color gradient, but Excel doesn't accept cell references in this type of definition.

Don't believe that I can figure out a method to use a 3-color gradient that does +/- values properly. What you want is a 5 color gradient:
[pre]
-90 -45 0 45 90
RED--YELLOW--GREEN--YELLOW--RED
[/pre]

So my solution is to
1) make a column of absolute values
2) apply a single 3-color CF to that column
3) run a macro that assigns the DisplayFormat.Interior.Color to the cells in your column

Sub ShadeCells()
    Dim r As Range, rng As Range
    
    Set rng = [B2]
    Set rng = Range(rng, rng.End(xlDown))
    
    For Each r In rng
        r.Interior.Color = r.Offset(0, -1).DisplayFormat.Interior.Color
    Next
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]