Applying more than 3 conditional formats

M

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
 
D

dodong

Matthew said:
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
Click to expand...
 
D

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
 
M

Matthew Hodgson

Dodong,

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

Matthew
 
Top