R – Excel 2007 3-color scale with negative values

conditional-formattingexcelvba

Given the following data

values
 12
 2
 0
-2
-12

I want to add a 3-color scale conditional formatting to the values column such that cells

< 0 are green
= 0 are white
> 0 are red

Using formulas like

=$A$1<0 for Minimum
=$A$1=0 for Midpoint
=$A$1>0 for Masximum

does not seem to work. cell value > 0 is red, cell < 0 is green, but cell = 0 is NOT white (it is green)

What's the right way to define the formulas?

Best Solution

  • Select the cells and choose the color white, it will be default
  • Put the rules for less than 0 and greater than 0 only