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.

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




"Melanie" wrote:

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.

Thanks Mike, a great help.

Melanie

"Mike H" wrote:

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




"Melanie" wrote:

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.