Just as a note. It is very good practice to AVOID MERGED CELLS. They cause all kinds of problems and interfer with many Excel capabilities. Far better to use "Center Across Selection" which accomplishes the same thing and allows Excel to use all of its capabilities.
old, slow, and confused
but at least I'm inconsistent!
Thanks, I wasn't aware of the many issues merged cells caused. I changed cell V1 so it's no longer merged.
I'll state my situation again. I would like to change the tab color of all 6 worksheets in my workbook. If there is a value in cell V1 of the first worksheet, I would like all 6 tabs to be red. If cell V1 is blank, I don't want any coloring on any of the 6 tabs.
Here's the code I've been using but no matter what I have in cell V1, all the tabs are red.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim i As Long
If InStr(Target.Address, "$A$7") <> 0 Or InStr(Target.Address, "$A$8") <> 0 Then
For Each ws In Worksheets
i = i + 1
On Error Resume Next
If Not IsDate(Range("A7")) Then
ws.Name = "Cert Period " & i
ws.Name = Format(ws.Range("A7"), "m-dd-yy") & " THRU " & Format(ws.Range("F7"), "m-dd-yy")
If Err.Number <> 0 Then
MsgBox "Could not rename sheet " & ws.Name, vbCritical, "Renaming Error"
If Not IsDate(Range("V1")) Then
ws.Tab.ColorIndex = -xlColorIndexAutomatic
ws.Tab.ColorIndex = 3
All tabs for all 6 worksheets are red which is what I want when there is a value in cell V1. I need to fix it so there's no color if cell V1 is empty.
FYI - if cell V1 in the first worksheet has a value in it, that value is copied to cell V1 of the rest of the worksheets. So it doesn't matter which worksheet is referenced, if there's a value in cell V1 of any of the worksheets, I want all tabs to be red. If cell V1 of any of the worksheets is empty, I want all the tabs to not be colored.