I'm not sure what you mean when you ask . . . . What's in the leftmost
worksheet in cell A7?

I tried using your code but it doesn't work with the names of the
sheets with Cert Period & i when cell A7 is blank. I inserted your
part of the code with the coloring of the tabs and then I made some
changes but I keep getting the same result that all the tabs are
colored when I use your code and when I made the change.

The cell I want to base coloring or not coloring the tabs is cell V1.
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.


Code:
--------------------
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
Else
ws.Name = Format(ws.Range("A7"), "m-dd-yy") & " THRU " & Format(ws.Range("F7"), "m-dd-yy")
End If
If Err.Number < 0 Then
MsgBox "Could not rename sheet " & ws.Name, vbCritical, "Renaming Error"
Err.Clear
End If
If Not IsDate(Range("V1")) Then
ws.Tab.ColorIndex = -xlColorIndexAutomatic
Else
ws.Tab.ColorIndex = 3
End If
Next ws

End If
End Sub
--------------------


Cell V1 is not merged. It's formatted with a date format. I'm not
sure what the problem could be.


--
Zenaida


------------------------------------------------------------------------
Zenaida's Profile: http://www.excelforum.com/member.php...o&userid=33802
View this thread: http://www.excelforum.com/showthread...hreadid=536203