Conditional Formatting

M

Melanie

I need to format 5 conditions in a particular spreadsheet. I can add the 4th
by using the fill option over the area but having problems adding the 5th.
Any ideas? Thanks.
 
M

Mike H

Melanie,

You can have an unlimited amount with the worksheet_change event and select
case.

Right click your sheet tab|view code and paste this in:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim colour As Integer

If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Select Case Target
Case 1
colour = 6
Case 2
colour = 12
Case 3
colour = 7
Case 4
colour = 53
Case 5
colour = 15
Case 6
colour = 42
Case Else
'do nothing
End Select

Target.Interior.ColorIndex = colour
End If

End Sub

Alter what thecase is to suit for example
Case Is = "some text"

or

Case Is > 99

Mike
 
M

Melanie

Thanks Mike, a great help.

Melanie

Mike H said:
Melanie,

You can have an unlimited amount with the worksheet_change event and select
case.

Right click your sheet tab|view code and paste this in:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim colour As Integer

If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Select Case Target
Case 1
colour = 6
Case 2
colour = 12
Case 3
colour = 7
Case 4
colour = 53
Case 5
colour = 15
Case 6
colour = 42
Case Else
'do nothing
End Select

Target.Interior.ColorIndex = colour
End If

End Sub

Alter what thecase is to suit for example
Case Is = "some text"

or

Case Is > 99

Mike
Click to expand...
 
Top