VBA Code help

[jlabrecque]

I am trying to write a code that changes the color of tabs based on a count if function. The problem I am running into is that for the changes to take place, I have to click on one of the cells and press enter, and then all the tabs change to the appropriate color. I want the tab colors to change progressively as the total gets higher or lower.

Please see the code below

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Ws3 As Worksheet
Dim Ws4 As Worksheet

Set Ws1 = Worksheets("1")
Set Ws2 = Worksheets("2")
Set Ws3 = Worksheets("3")
Set Ws4 = Worksheets("4")

If Range("Risk1").Value >= 5 Then
Ws1.Tab.ColorIndex = 3
ElseIf Range("Risk1") >= 3 Then
Ws1.Tab.ColorIndex = 6
ElseIf Range("Risk1") <> 0 Then
Ws1.Tab.ColorIndex = 10
Else
Ws1.Tab.ColorIndex = xlColorIndexNone
End If

If Range("Risk2").Value >= 5 Then
Ws2.Tab.ColorIndex = 3
ElseIf Range("Risk2") >= 3 Then
Ws2.Tab.ColorIndex = 6
ElseIf Range("Risk2") <> 0 Then
Ws2.Tab.ColorIndex = 10
Else
Ws2.Tab.ColorIndex = xlColorIndexNone
End If

If Range("Risk3").Value >= 5 Then
Ws3.Tab.ColorIndex = 3
ElseIf Range("Risk3") >= 3 Then
Ws3.Tab.ColorIndex = 6
ElseIf Range("Risk3") <> 0 Then
Ws3.Tab.ColorIndex = 10
Else
Ws3.Tab.ColorIndex = xlColorIndexNone
End If


If Range("Risk4").Value >= 5 Then
Ws4.Tab.ColorIndex = 3
ElseIf Range("Risk4") >= 3 Then
Ws4.Tab.ColorIndex = 6
ElseIf Range("Risk4") <> 0 Then
Ws4.Tab.ColorIndex = 10
Else
Ws4.Tab.ColorIndex = xlColorIndexNone
End If

End Sub


Thank you in advance

 

[JoeMo]

Do the named ranges "RiskN" where N= 1,2,3,4 hold formulas or constants that are changed by the user? If the former, consider using a Worksheet_Calculate event rather than Worksheet_Change. If the latter, then you need the Worksheet_Change for the tab colors to change progressively as the total gets higher or lower.

 

[jlabrecque]

Hi Joe,

That was the answer thanks for your help.

 

[JoeMo]

Hi Joe,

That was the answer thanks for your help.

You are welcome - thanks for the reply.