Applying more than 3 conditional formats

Hi there!

I would like to apply a different cell colours to a range based on the cell
value compared with a fixed scale of values.

e.g. values between
0-10 get "green" cell background
11-20 get "orange" cell background
21-30 get "red" cell background
31-40 get "purple" cell background
41-50 get "blue" cell background

I am aware that you can use conditional formatting to produce this effect,
but I'd like to show more than 3 colours.

Is this possible? Or what are the alternatives to getting the same result?

Any help you can give me greatly appreciated.

Thanks,

Matthew

Matthew Hodgson wrote:
> Hi there!
>
> I would like to apply a different cell colours to a range based on the cell
> value compared with a fixed scale of values.
>
> e.g. values between
> 0-10 get "green" cell background
> 11-20 get "orange" cell background
> 21-30 get "red" cell background
> 31-40 get "purple" cell background
> 41-50 get "blue" cell background
>
> I am aware that you can use conditional formatting to produce this effect,
> but I'd like to show more than 3 colours.
>
> Is this possible? Or what are the alternatives to getting the same result?
>
> Any help you can give me greatly appreciated.
>
> Thanks,
>
> Matthew

Just copy this code to your worksheet and change the color Index
property if you want to change the back ground color.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Value
Case Is <= 10
Target.Interior.ColorIndex = 3
Case Is <= 20
Target.Interior.ColorIndex = 8
Case Is <= 30
Target.Interior.ColorIndex = 15
Case Is <= 40
Target.Interior.ColorIndex = 6
Case Else
Target.Interior.ColorIndex = 12
End Select
End Sub

Regards
Dodong

Dodong,

Thanks for your help - and quick response! Works a treat. . .

Matthew

"dodong" <[email protected]> wrote in message
news:[email protected]...
> Just copy this code to your worksheet and change the color Index
> property if you want to change the back ground color.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Select Case Target.Value
> Case Is <= 10
> Target.Interior.ColorIndex = 3
> Case Is <= 20
> Target.Interior.ColorIndex = 8
> Case Is <= 30
> Target.Interior.ColorIndex = 15
> Case Is <= 40
> Target.Interior.ColorIndex = 6
> Case Else
> Target.Interior.ColorIndex = 12
> End Select
> End Sub
>
> Regards
> Dodong
>