tab color

[briank]

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


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


"briank" <briank@discussions.microsoft.com> wrote in message
news:ABCD25DF-6CF2-4499-8E3C-3A424BA56D51@microsoft.com...
>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

[briank]

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" <briank@discussions.microsoft.com> wrote in message
> news:ABCD25DF-6CF2-4499-8E3C-3A424BA56D51@microsoft.com...
> >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
>
>
>

[briank]

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" <briank@discussions.microsoft.com> wrote in message
> news:ABCD25DF-6CF2-4499-8E3C-3A424BA56D51@microsoft.com...
> >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]

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" <briank@discussions.microsoft.com> wrote in message
news:0919E2CF-BDDE-435F-A69C-D9B2042C9A12@microsoft.com...
> 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" <briank@discussions.microsoft.com> wrote in message
>> news:ABCD25DF-6CF2-4499-8E3C-3A424BA56D51@microsoft.com...
>> >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
>>
>>
>>

[MSP77079]

Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Application.WorksheetFunction.Sum(Sh.Range("A1:A50")) > 0 Then Sh.Tab.ColorIndex = 3

End Sub

- Pete