software

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, quot;$A$7quot;) lt;gt; 0 Or InStr(Target.Address, quot;$A$8quot;) lt;gt; 0 Then
For Each ws In Worksheets
i = i 1
On Error Resume Next
If Not IsDate(Range(quot;A7quot;)) Then
ws.Name = quot;Cert Period quot; amp; i
Else
ws.Name = Format(ws.Range(quot;A7quot;), quot;m-dd-yyquot;) amp; quot; THRU quot; amp; Format(ws.Range(quot;F7quot;), quot;m-dd-yyquot;)
End If
If Err.Number lt;gt; 0 Then
MsgBox quot;Could not rename sheet quot; amp; ws.Name, vbCritical, quot;Renaming Errorquot;
Err.Clear
End If
Next ws
End If
End Sub
--------------------(FYI - cells A7 amp; F7 are merged cells.)

Any help is greatly appreciated. Thanks. -------------------------------------------------------------------
|Filename: Frequency Audit.zip |
|Download: www.excelforum.com/attachment.php?postid=4696 |
-------------------------------------------------------------------

--
Zenaida------------------------------------------------------------------------
Zenaida's Profile: www.excelforum.com/member.php...oamp;userid=33802
View this thread: www.excelforum.com/showthread...hreadid=536203Could you use worksheets(1)?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim i As Long

If Target.Cells.Count gt; 1 Then Exit Sub
If Intersect(Target, Me.Range(quot;a7:a8quot;)) Is Nothing Then Exit Sub

For Each ws In Worksheets
i = i 1
On Error Resume Next
'did you mean ws.range(quot;a7quot;)
'or me.range(quot;a7quot;)
If IsDate(ws.Range(quot;A7quot;)) Then
ws.Name = Format(ws.Range(quot;A7quot;), quot;m-dd-yyquot;) _
amp; quot; THRU quot; amp; Format(ws.Range(quot;F7quot;), quot;m-dd-yyquot;)
Else
ws.Name = quot;Cert Period quot; amp; i
End If
If Err.Number lt;gt; 0 Then
MsgBox quot;Could not rename sheet quot; amp; ws.Name, vbCritical, _
quot;Renaming Errorquot;
Err.Clear
End If
If Me.Parent.Worksheets(1).Range(quot;a7quot;) = quot;quot; Then
'do nothing
Else
ws.Tab.ColorIndex = 3
End If
Next ws

End Sub

I (arbitrarily) changed a couple of things. You can change them back if you
don't like them.

I find this more difficult to understand:
If InStr(Target.Address, quot;$A$7quot;) lt;gt; 0 Or InStr(Target.Address, quot;$A$8quot;) lt;gt; 0 Then
than:
If Intersect(Target, Me.Range(quot;a7:a8quot;)) Is Nothing Then Exit Sub

And instead of checking the negative, I like to check the positive. But that
means the Then portion and the Else portion have to swap locations:

If Not IsDate(Range(quot;A7quot;)) Then
ws.Name = quot;Cert Period quot; amp; i
Else
ws.Name = Format(ws.Range(quot;A7quot;), quot;m-dd-yyquot;) _
amp; quot; THRU quot; amp; Format(ws.Range(quot;F7quot;), quot;m-dd-yyquot;)
End If

becomes

If IsDate(ws.Range(quot;A7quot;)) Then
ws.Name = Format(ws.Range(quot;A7quot;), quot;m-dd-yyquot;) _
amp; quot; THRU quot; amp; Format(ws.Range(quot;F7quot;), quot;m-dd-yyquot;)
Else
ws.Name = quot;Cert Period quot; amp; i
End If

Zenaida wrote:
gt;
gt; Does anyone know what code I would use in the worksheet event to change
gt; the tab color of all 6 worksheets in my workbook based off a value in a
gt; cell?
gt;
gt; I would like all 6 tabs to be red if there is any value in cell V1 of
gt; worksheet 1. If cell V1 is empty I don't want any tab color.
gt;
gt; I'm also not sure how to reference worksheet 1 in the code because the
gt; name of it changes depending on what's in two other cells of the
gt; worksheet.
gt;
gt; This is the code I have right now in the worksheet event.
gt;
gt; Code:
gt; --------------------
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; Dim ws As Worksheet
gt; Dim i As Long
gt;
gt; If InStr(Target.Address, quot;$A$7quot;) lt;gt; 0 Or InStr(Target.Address, quot;$A$8quot;) lt;gt; 0 Then
gt; For Each ws In Worksheets
gt; i = i 1
gt; On Error Resume Next
gt; If Not IsDate(Range(quot;A7quot;)) Then
gt; ws.Name = quot;Cert Period quot; amp; i
gt; Else
gt; ws.Name = Format(ws.Range(quot;A7quot;), quot;m-dd-yyquot;) amp; quot; THRU quot; amp; Format(ws.Range(quot;F7quot;), quot;m-dd-yyquot;)
gt; End If
gt; If Err.Number lt;gt; 0 Then
gt; MsgBox quot;Could not rename sheet quot; amp; ws.Name, vbCritical, quot;Renaming Errorquot;
gt; Err.Clear
gt; End If
gt; Next ws
gt; End If
gt; End Sub
gt; --------------------
gt;
gt; (FYI - cells A7 amp; F7 are merged cells.)
gt;
gt; Any help is greatly appreciated. Thanks.
gt;
gt; -------------------------------------------------------------------
gt; |Filename: Frequency Audit.zip |
gt; |Download: www.excelforum.com/attachment.php?postid=4696 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; Zenaida
gt;
gt; ------------------------------------------------------------------------
gt; Zenaida's Profile: www.excelforum.com/member.php...oamp;userid=33802
gt; View this thread: www.excelforum.com/showthread...hreadid=536203

--

Dave Peterson


Thanks for your help on the code. The tab coloring isn't quite right.
No matter what I have in the cell, all the tabs are red. Any ideas?--
Zenaida------------------------------------------------------------------------
Zenaida's Profile: www.excelforum.com/member.php...oamp;userid=33802
View this thread: www.excelforum.com/showthread...hreadid=536203This is the portion that does the checking/work:

If Me.Parent.Worksheets(1).Range(quot;a7quot;) = quot;quot; Then
'do nothing
Else
ws.Tab.ColorIndex = 3
End If

What's in the leftmost worksheet in cell A7?

As long as there's something in it, the tab color will be red.

Zenaida wrote:
gt;
gt; Thanks for your help on the code. The tab coloring isn't quite right.
gt; No matter what I have in the cell, all the tabs are red. Any ideas?
gt;
gt; --
gt; Zenaida
gt;
gt; ------------------------------------------------------------------------
gt; Zenaida's Profile: www.excelforum.com/member.php...oamp;userid=33802
gt; View this thread: www.excelforum.com/showthread...hreadid=536203

--

Dave Peterson


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 amp; 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, quot;$A$7quot;) lt;gt; 0 Or InStr(Target.Address, quot;$A$8quot;) lt;gt; 0 Then
For Each ws In Worksheets
i = i 1
On Error Resume Next
If Not IsDate(Range(quot;A7quot;)) Then
ws.Name = quot;Cert Period quot; amp; i
Else
ws.Name = Format(ws.Range(quot;A7quot;), quot;m-dd-yyquot;) amp; quot; THRU quot; amp; Format(ws.Range(quot;F7quot;), quot;m-dd-yyquot;)
End If
If Err.Number lt;gt; 0 Then
MsgBox quot;Could not rename sheet quot; amp; ws.Name, vbCritical, quot;Renaming Errorquot;
Err.Clear
End If
If Not IsDate(Range(quot;V1quot;)) 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: www.excelforum.com/member.php...oamp;userid=33802
View this thread: www.excelforum.com/showthread...hreadid=536203I messed up when I used A7. It should have been V1.

If Me.Parent.Worksheets(1).Range(quot;a7quot;) = quot;quot; Then
ws.tab.colorindex = xlNone
Else
ws.Tab.ColorIndex = 3
End If
Zenaida wrote:
gt;
gt; I'm not sure what you mean when you ask . . . . What's in the leftmost
gt; worksheet in cell A7?
gt;
gt; I tried using your code but it doesn't work with the names of the
gt; sheets with Cert Period amp; i when cell A7 is blank. I inserted your
gt; part of the code with the coloring of the tabs and then I made some
gt; changes but I keep getting the same result that all the tabs are
gt; colored when I use your code and when I made the change.
gt;
gt; The cell I want to base coloring or not coloring the tabs is cell V1.
gt; FYI - if cell V1 in the first worksheet has a value in it, that value
gt; is copied to cell V1 of the rest of the worksheets. So it doesn't
gt; matter which worksheet is referenced, if there's a value in cell V1 of
gt; any of the worksheets, I want all tabs to be red. If cell V1 of any of
gt; the worksheets is empty, I want all the tabs to not be colored.
gt;
gt; Code:
gt; --------------------
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; Dim ws As Worksheet
gt; Dim i As Long
gt;
gt; If InStr(Target.Address, quot;$A$7quot;) lt;gt; 0 Or InStr(Target.Address, quot;$A$8quot;) lt;gt; 0 Then
gt; For Each ws In Worksheets
gt; i = i 1
gt; On Error Resume Next
gt; If Not IsDate(Range(quot;A7quot;)) Then
gt; ws.Name = quot;Cert Period quot; amp; i
gt; Else
gt; ws.Name = Format(ws.Range(quot;A7quot;), quot;m-dd-yyquot;) amp; quot; THRU quot; amp; Format(ws.Range(quot;F7quot;), quot;m-dd-yyquot;)
gt; End If
gt; If Err.Number lt;gt; 0 Then
gt; MsgBox quot;Could not rename sheet quot; amp; ws.Name, vbCritical, quot;Renaming Errorquot;
gt; Err.Clear
gt; End If
gt; If Not IsDate(Range(quot;V1quot;)) Then
gt; ws.Tab.ColorIndex = -xlColorIndexAutomatic
gt; Else
gt; ws.Tab.ColorIndex = 3
gt; End If
gt; Next ws
gt;
gt; End If
gt; End Sub
gt; --------------------
gt;
gt; Cell V1 is not merged. It's formatted with a date format. I'm not
gt; sure what the problem could be.
gt;
gt; --
gt; Zenaida
gt;
gt; ------------------------------------------------------------------------
gt; Zenaida's Profile: www.excelforum.com/member.php...oamp;userid=33802
gt; View this thread: www.excelforum.com/showthread...hreadid=536203

--

Dave Peterson


I did notice you used A7 instead of V1. I changed a7 to v1 in the code
and when v1 is empty, nothing happens, when v1 has a date in it,
nothing happens.

If I leave a7 in the code instead of v1 and enter a date in a7, all the
tabs will turn red, when I remove the date from a7, all the tabs have no
color - which is really great but why can't I change the code to v1 and
have it work the way it does with a7?--
Zenaida------------------------------------------------------------------------
Zenaida's Profile: www.excelforum.com/member.php...oamp;userid=33802
View this thread: www.excelforum.com/showthread...hreadid=536203It looks like you used a cross between the code I suggested and your existing
code. How about just trying the code I suggested (with the A7/V1 change) and
see what happens?Zenaida wrote:
gt;
gt; I did notice you used A7 instead of V1. I changed a7 to v1 in the code
gt; and when v1 is empty, nothing happens, when v1 has a date in it,
gt; nothing happens.
gt;
gt; If I leave a7 in the code instead of v1 and enter a date in a7, all the
gt; tabs will turn red, when I remove the date from a7, all the tabs have no
gt; color - which is really great but why can't I change the code to v1 and
gt; have it work the way it does with a7?
gt;
gt; --
gt; Zenaida
gt;
gt; ------------------------------------------------------------------------
gt; Zenaida's Profile: www.excelforum.com/member.php...oamp;userid=33802
gt; View this thread: www.excelforum.com/showthread...hreadid=536203

--

Dave Peterson


When I use your code exactly how you have it with the a7/v1 conversion,
nothing happens. The tabs don't change color with a value in v1 and
they don't change when v1 is empty.--
Zenaida------------------------------------------------------------------------
Zenaida's Profile: www.excelforum.com/member.php...oamp;userid=33802
View this thread: www.excelforum.com/showthread...hreadid=536203Please post the code you tried that failed.

Zenaida wrote:
gt;
gt; When I use your code exactly how you have it with the a7/v1 conversion,
gt; nothing happens. The tabs don't change color with a value in v1 and
gt; they don't change when v1 is empty.
gt;
gt; --
gt; Zenaida
gt;
gt; ------------------------------------------------------------------------
gt; Zenaida's Profile: www.excelforum.com/member.php...oamp;userid=33802
gt; View this thread: www.excelforum.com/showthread...hreadid=536203

--

Dave Peterson