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



"Hawksby" wrote:

Is there a way of having 5 or more conditional formats?

This section does the work:

Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green

So you can change it the way you like:

Case "some text value": .Interior.ColorIndex = 3 'red
Case "something": .Interior.ColorIndex = 6 'yellow
Case "another thing": .Interior.ColorIndex = 5 'blue
Case 12344: .Interior.ColorIndex = 10 'green
case "that was a real number above": .interior.colorindex = 23

If you're not sure what the colors should be,
then add a new worksheet
record a macro when you change some background colors
Stop the macro

look at the code to see what excel recorded.

Hawksby wrote:

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

"Hawksby" wrote:

Is there a way of having 5 or more conditional formats?

--

Dave Peterson

Dave,

Do i need to change any other part of the code? as i'm having difficulty in
getting it to work

"Hawksby" wrote:

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



"Hawksby" wrote:

Is there a way of having 5 or more conditional formats?

Maybe...

You have to specify the cells that get changed in this line:

Const WS_RANGE As String = "H1:H10"

And make sure you put the code behind the worksheet that should have this
behavior.

Rightclick on the worksheet tab, select view code and make sure your code is
there. Delete other versions if you pasted them elsewhere.

And if this doesn't help, you're gonna have to explain what you tried and what
failed.

Hawksby wrote:

Dave,

Do i need to change any other part of the code? as i'm having difficulty in
getting it to work

"Hawksby" wrote:

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



"Hawksby" wrote:

Is there a way of having 5 or more conditional formats?


--

Dave Peterson