Excel 2007/2010 color scale conditional formatting based on formula

Question

Suppose I have the following table structure (A1:C3)

     A   B   C
1    H1  H2  H3
2    1   1   3
3    4   5   4

How can I apply a conditional formatting with the following conditions:

• Only the header cells (H1,H2,H3) are colored
• The coloring scheme is 2 or 3-color-scale
• The values used for computing the color should be A2/A3, B2/B3, C2/C3 (range is 0-1)

Note: I'm not looking for a VBA solution, as I can make my own, but if you have some crazy 1 liner please tell :)
Note: I don't want to apply a lot of rules like if x<0.3 red, if 0.3<x<0.6 orange etc.

Is there any clean solution for this?
Can a 3-color-scale be applied based on the value of a formula?

Answer 1

I think you're looking for something nifty that unfortunately doesn't exist. I just see the two options.

You can either do conditional formatting with three conditions, or even just two if you want to use the default cell color as one of the three (e.g. Formula Is =(A2/A3)<0.3, Formula Is =(A2/A3)<0.6, or whatever you want your threshold values to be). You can apply this fomatting manually on one header cell in Excel, and copy it across to all headers; or apply it through VBA.

Or you can color the headers in VBA, and place a button on your sheet that calls the code to update the colors.

With Range("A1")
    For i = 1 To 3
        colorscale = .Cells(2, i).Value / .Cells(3, i).Value
        .Cells(1, i).Interior.Color = _
            RGB(colorscale * 255, colorscale * 255, _
            colorscale * 255) ' for example. Or pick your colors otherwise.
    Next i
End With

Sounds to me like the first option (conditional formatting) is better suited, because it will update the colors automatically based on the cell values. I have to admit, though, I don't quite see why you're looking for something "cleaner" than this, because it's actually a really simple solution. Do two conditions constitute "a lot of rules"? Not by any stretch of the English language...