Geek Answers Handbook

Excel 2007/2010 conditional formatting of a color scale based on a formula

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 to apply conditional formatting with the following conditions:

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

Note. I'm not looking for a VBA solution, as I can make my own, but if you have a crazy 1 liner, tell me :)
Note. I do not want to apply many rules, such as if x<0.3 red, if 0.3<x<0.6 orangeetc.

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

+5
2

, - , , , . .

, , (, Formula Is = (A2/A3) < 0.3, Formula Is = (A2)/A3)<0.6, , , ). fomatting Excel ; VBA.

VBA , .

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

, ( ) , . , , - " ", , . " "? - ...

+1

- , -- :

With Range("A1")
    For i = 1 To 3
        ColorScale = .Cells(2, i).Value / .Cells(3, i).Value
        If ColorScale < 0.5 Then
            red = 255
            green = ColorScale * 2 * 255
        Else
            red = (1 - ColorScale) * 2 * 255
            green = 255
        End If

        .Cells(1, i).Interior.Color = RGB(red, green, 0)
    Next i
End With
0

More articles:


All Articles