H
Hawksby
Hi,
I need to add more than 5 conditional formats to my spreadsheet. Joel
responded to me with the following but unforunately my VB is not the
greatest. Where do i put in my arguments etc to make it go red or blue etc?
Thsi si a very popular question. It is asked a few times a week. Here is
the answer somebody provided earlier this week. It shows four conditions but
can easily be changes to havve many more conditions Just add more case
statements to the function.
-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
I need to add more than 5 conditional formats to my spreadsheet. Joel
responded to me with the following but unforunately my VB is not the
greatest. Where do i put in my arguments etc to make it go red or blue etc?
Thsi si a very popular question. It is asked a few times a week. Here is
the answer somebody provided earlier this week. It shows four conditions but
can easily be changes to havve many more conditions Just add more case
statements to the function.
-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub