tab color

[Guest]

I would like to alter the color of the tab based upon a value in an array of
cells. In my example if the range is A1:A50 and just one cell has a number in
it then this will cause the tab to change color. Here is some code that I
found but cannot get it to work. Any suggestions?
i.e.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1..$A$50" Then
If Target.Value>0 Then Sh.Tab.ColorIndex = 3
End If
End Sub

 

[Rob van Gelder]

Right-click the Tab, View Code, Paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.WorksheetFunction.CountA(Range("A1:A50")) = 1 Then
Target.Parent.Tab.ColorIndex = 3
Else
Target.Parent.Tab.ColorIndex = 5
End If
End Sub

 

[Guest]

Thank you. It works gr8!

Right-click the Tab, View Code, Paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.WorksheetFunction.CountA(Range("A1:A50")) = 1 Then
Target.Parent.Tab.ColorIndex = 3
Else
Target.Parent.Tab.ColorIndex = 5
End If
End Sub

 

[Guest]

Follow Up: If I change the spreadsheet to elimate data in the cell range
A1..A50, the tab still stays the same color. Is there a "refresh" command
that I can add?

 

[Rob van Gelder]

The code works as so:
Whenever the worksheet changes, it counts the number of entries in A1:A50
If it counts 1 then color is 3 otherwise the color is 5

To see whether the code is running or not try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.WorksheetFunction.CountA(Range("A1:A50")) = 1 Then
MsgBox "Triggered 1"
' Target.Parent.Tab.ColorIndex = 3
Else
MsgBox "Triggered not 1"
' Target.Parent.Tab.ColorIndex = 5
End If
End Sub