Does anyone know what code I would use in the worksheet event to change
the tab color of all 6 worksheets in my workbook based off a value in a
cell?

I would like all 6 tabs to be red if there is any value in cell V1 of
worksheet 1. If cell V1 is empty I don't want any tab color.

I'm also not sure how to reference worksheet 1 in the code because the
name of it changes depending on what's in two other cells of the
worksheet.

This is the code I have right now in the worksheet event.


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
Next ws
End If
End Sub
--------------------


(FYI - cells A7 & F7 are merged cells.)

Any help is greatly appreciated. Thanks.


+-------------------------------------------------------------------+
|Filename: Frequency Audit.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4696 |
+-------------------------------------------------------------------+

--
Zenaida


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