Applying more than 3 conditional formats

[Matthew Hodgson]

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

 

[dodong]

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

 

[dodong]

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

 

[Matthew Hodgson]

Dodong,

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

Matthew