Color cells by absolute value in a range in Excel 2010
I'm looking to color a table of values in Excel 2010 by their absolute value. Basically, if I have the table:
...the cells are colored by the cell's raw value. What I would like to do is color by the cell's absolute value, so with the cell coloring of this table:
...but with the values of the first table (the real values). Any ideas on how one might do this? Through the GUI or with VBA?
Answer
I don't think that there is any way to do this with three colors (red, yellow, green), but you can do it with two colors (for example yellow and green). Simply make the color for the low value and the color for the high value the same. That way, the cells with the lower absolute value will have the middle color and cells with the higher absolute value will have the other color.
- Select Your data
- Conditional Formatting
- Color Scale
- More Rules
- Select "3-Point Scale" under Format Style
- Change the colors so that the Maximum and Minimum colors are the same