How do I get this Macro to apply to ALL sheets in workbook?

[Guest]

Have tried various methods and can't seem to get this Macro to apply to all
the worksheets (January, Feb, March, etc.) in the workbook. Can only get it
to apply to the first one, so you'll see that the January one is the only one
listed below....

Sub StartBlink()
With ThisWorkbook.Worksheets("January").Range("d2:d4").Font
If .ColorIndex = 3 Then ' Red Text
.ColorIndex = 2 ' Yellow Text
Else
.ColorIndex = 3 ' Red Text
End If
End With
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", ,
True
End Sub

 

[Guest]

This should get it *untested*

Sub StartBlink()
for i=1 to sheets.count
With ThisWorkbook.Worksheets(i).Range("d2:d4").Font
If .ColorIndex = 3 Then ' Red Text
.ColorIndex = 2 ' Yellow Text
Else
.ColorIndex = 3 ' Red Text
End If
End With
next
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", ,
True
End Sub

 

[George Nicholson]

Not sure you really meant "apply to all sheets" or if you meant "apply to
any active sheet". If you meant just the ActiveSheet (which is *usually* the
only one the user can see, so there's little point in having the others
blink) then I'd just change
ThisWorkbook.Worksheets("January")
to
ActiveSheet.

On the other hand, it is possible to have more than one sheet/window visible
at a time. So if you really did mean "apply to all
the worksheets in the workbook" then you could try the following. This code
was tested while in a General Module (VB editor>Insert>Module). I also added
an argument that you'll want to remove if you were planning on running this
via Tools>Macros.

Sub StartBlink(Optional bRunBlink As Boolean = True)
Dim dRunWhen As Date
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
With wks.Range("d2:d4").Font
If .ColorIndex = 3 Then ' Red Text
.ColorIndex = 2 ' Yellow Text
Else
.ColorIndex = 3 ' Red Text
End If
End With
Next wks
dRunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime dRunWhen, "StartBlink", , bRunBlink
End Sub

StartBlink to start it, StartBlink(False) to stop it.

Note that putzing with screen stuff and running a piece of code every second
BOTH carry performance penalties, so if you only need the active sheet to
blink then limit yourself to that.

HTH,