tab color
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 |
tab color
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 |
tab color
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 |
tab color
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 |
tab color
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 |
All times are GMT +1. The time now is 01:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com