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.Value0 Then Sh.Tab.ColorIndex = 3
End If
End Sub

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"briank" wrote in message
...
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.Value0 Then Sh.Tab.ColorIndex = 3
End If
End Sub

Thank you. It works gr8!

"Rob van Gelder" wrote:

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"briank" wrote in message
...
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.Value0 Then Sh.Tab.ColorIndex = 3
End If
End Sub

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" wrote:

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"briank" wrote in message
...
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.Value0 Then Sh.Tab.ColorIndex = 3
End If
End Sub

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"briank" wrote in message
...
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" wrote:

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"briank" wrote in message
...
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.Value0 Then Sh.Tab.ColorIndex = 3
End If
End Sub