VBA to change Tab Color

[dchaney]

First i would like to thank JoeMo for the following code, but I have questions about it...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range, vRng As Range
Set myRng = Range("C14")
If Intersect(myRng, Target) Is Nothing Then Exit Sub
For i = 1 To myRng.Areas.Count
    For Each c In myRng.Areas(i).Cells
        If Not IsEmpty(c) And (c.Value > 5) Then
            Me.Tab.ColorIndex = 3
            Exit Sub
        End If
    Next c
    Me.Tab.ColorIndex = 4
Next i
End Sub

I have modified the original code a bit, but what I am trying to do is make the tab colors change automaticly. Currently I have to double click within cell C1 in order for the tab to change if there is a change in the number. I would like to know if there is something I can add to this code that will make the tab change color automaticly (My C! cell is a formula to give an average of days)

 

[VoG]

Maybe

Private Sub Worksheet_Calculate()
If Range("C14").Value > 5 Then
    Me.Tab.ColorIndex = 3
Else
    Me.Tab.ColorIndex = 4
End If
End Sub

 

[Rick Rothstein]

First i would like to thank JoeMo for the following code, but I have questions about it...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range, vRng As Range
Set myRng = Range("C14")
If Intersect(myRng, Target) Is Nothing Then Exit Sub
For i = 1 To myRng.Areas.Count
    For Each c In myRng.Areas(i).Cells
        If Not IsEmpty(c) And (c.Value > 5) Then
            Me.Tab.ColorIndex = 3
            Exit Sub
        End If
    Next c
    Me.Tab.ColorIndex = 4
Next i
End Sub

I have modified the original code a bit, but what I am trying to do is make the tab colors change automaticly. Currently I have to double click within cell C1 in order for the tab to change if there is a change in the number. I would like to know if there is something I can add to this code that will make the tab change color automaticly (My C! cell is a formula to give an average of days)

If your formula points to other cells on the same sheet the formula is in, then you can use this code...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim TargetRange As Range, vRng As Range
  Set TargetRange = Intersect(Target.Dependents, Range("C14"))
  If Not TargetRange Is Nothing Then
    If TargetRange.Value > 5 Then
      Me.Tab.ColorIndex = 3
    Else
      Me.Tab.ColorIndex = xlColorIndexNone
    End If
    Exit Sub
  End If
End Sub

 

[dchaney]

If your formula points to other cells on the same sheet the formula is in, then you can use this code...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim TargetRange As Range, vRng As Range
  Set TargetRange = Intersect(Target.Dependents, Range("C14"))
  If Not TargetRange Is Nothing Then
    If TargetRange.Value > 5 Then
      Me.Tab.ColorIndex = 3
    Else
      Me.Tab.ColorIndex = xlColorIndexNone
    End If
    Exit Sub
  End If
End Sub

Rick this worked great, thank you very much