Conditional formatting - progressively darker/lighted shading

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to shade fields progressively darker or lighter depending on
the values. For example, I would like the following values to have the
following colours:
-5 dark red
-4 lighter red
-3 slightly lighter red
-2 etc, etc
1 light green
5 dark green

One step further - would it be possible to tell excel to work out which is
the lowest and highest number in the data set and work out for itself how
light or dark to shade the values e.g, if my dataset happened to have the
values -50, -40, etc excel would work out which was a low value and which was
a high one.
 
Conditional formatting would normally be used to do this. However, in
XL2003 and earlier versions, you are limited to a maximum of 3
conditional formats per cell, so you would have to develop a macro to
give you the range of colours you describe. I did this for a client
some time ago with seven colour bands, and to be honest the effect was
not as dramatic as he had hoped.

The MIN( ) function can find the lowest value in a range and MAX( )
the highest, so you could use these to scale your data.

Hope this helps.

Pete
 
Many thanks Pete

Pete_UK said:
Conditional formatting would normally be used to do this. However, in
XL2003 and earlier versions, you are limited to a maximum of 3
conditional formats per cell, so you would have to develop a macro to
give you the range of colours you describe. I did this for a client
some time ago with seven colour bands, and to be honest the effect was
not as dramatic as he had hoped.

The MIN( ) function can find the lowest value in a range and MAX( )
the highest, so you could use these to scale your data.

Hope this helps.

Pete
Click to expand...
 
Back
Top