Conditional Formatting

[Guest]

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.

 

[Guest]

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

 

[Guest]

Thanks Mike, a great help.

Melanie

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