Conditional formatting - progressively darker/lighted shading

[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.

 

[Pete_UK]

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

 

[Guest]

Many thanks Pete

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

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Many thanks Pete







- Show quoted text -

 

[MartinW]

Hi Jonny,

Sounds like a job for Superman!! While you are waiting,
Bob Phillips does a very good impression with his CF Plus.
It allows for up to 30 conditional formats.

Click here,
http://xldynamic.com/source/xld.CFPlus.Download.html

HTH
Martin