Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Tab Colors
Hi -
I'd like a short procedure to loop through a set number of worksheets (named 1 to 31), testing for the existence of ANY data in cell D3 (on each sheet) ... if NO DATA exists, the tab color should be changed to Red. This procedure will be used on computers running XP and XL02 ... Any help is greatly appreciated! Thanks, Ray |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Tab Colors
Give this macro a try...
Sub CheckCellD3() Dim WS As Worksheet For Each WS In Worksheets If WS.Range("D3").Value = "" Then WS.Tab.ColorIndex = 3 Else WS.Tab.ColorIndex = xlNone End If Next End Sub -- Rick (MVP - Excel) "Ray" wrote in message ... Hi - I'd like a short procedure to loop through a set number of worksheets (named 1 to 31), testing for the existence of ANY data in cell D3 (on each sheet) ... if NO DATA exists, the tab color should be changed to Red. This procedure will be used on computers running XP and XL02 ... Any help is greatly appreciated! Thanks, Ray |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Tab Colors
On Wed, 29 Jul 2009 14:27:30 -0400, "Rick Rothstein"
wrote: Give this macro a try... Sub CheckCellD3() Dim WS As Worksheet For Each WS In Worksheets If WS.Range("D3").Value = "" Then WS.Tab.ColorIndex = 3 Else WS.Tab.ColorIndex = xlNone End If Next End Sub And if you have other sheets in the workbook besides sheets 1,2,3,..31 that you don't want to include in this check you may replace the For Each WS in Worksheets with For i = 1 to 31 Set WS = Worksheets(""&i) Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Tab Colors
Good point! Thanks for picking up on that.
-- Rick (MVP - Excel) "Lars-Åke Aspelin" wrote in message ... On Wed, 29 Jul 2009 14:27:30 -0400, "Rick Rothstein" wrote: Give this macro a try... Sub CheckCellD3() Dim WS As Worksheet For Each WS In Worksheets If WS.Range("D3").Value = "" Then WS.Tab.ColorIndex = 3 Else WS.Tab.ColorIndex = xlNone End If Next End Sub And if you have other sheets in the workbook besides sheets 1,2,3,..31 that you don't want to include in this check you may replace the For Each WS in Worksheets with For i = 1 to 31 Set WS = Worksheets(""&i) Hope this helps / Lars-Åke |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Tab Colors
Rick & Lars-Åke -
Thanks very much ... worked perfectly! and I DID have more than just the Sheets titled 1-31, so Lars-Åke's suggestion was definitely useful! //ray On Jul 29, 2:52*pm, "Rick Rothstein" wrote: Good point! Thanks for picking up on that. -- Rick (MVP - Excel) "Lars-Åke Aspelin" wrote in message ... On Wed, 29 Jul 2009 14:27:30 -0400, "Rick Rothstein" wrote: Give this macro a try... Sub CheckCellD3() *Dim WS As Worksheet *For Each WS In Worksheets * *If WS.Range("D3").Value = "" Then * * *WS.Tab.ColorIndex = 3 * *Else * * *WS.Tab.ColorIndex = xlNone * *End If *Next End Sub And if you have other sheets in the workbook besides sheets 1,2,3,..31 that you don't want to include in this check you may replace the For Each WS in Worksheets with For i = 1 to 31 Set WS = Worksheets(""&i) Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Statements looking at colors? | Excel Discussion (Misc queries) | |||
Conditional Colors in a CELL | Excel Discussion (Misc queries) | |||
conditional text colors | Excel Discussion (Misc queries) | |||
Conditional Colors for Charts | Charts and Charting in Excel | |||
Conditional Colors for Charts | Charts and Charting in Excel |