Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I get this Macro to apply to ALL sheets in workbook?
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 -- News Gal |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I get this Macro to apply to ALL sheets in workbook?
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 -- -John Please rate when your question is answered to help us and others know what is helpful. "Newsgal" wrote: 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 -- News Gal |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I get this Macro to apply to ALL sheets in workbook?
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 editorInsertModule). I also added an argument that you'll want to remove if you were planning on running this via ToolsMacros. 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, "Newsgal" wrote in message ... 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 -- News Gal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Apply Conditional Format to all sheets in same workbook | Excel Discussion (Misc queries) | |||
Apply Macro to all worksheets in a workbook except one | Excel Discussion (Misc queries) | |||
apply Macro to all sheets in workbook - loop | Excel Programming | |||
how to apply the same header or footer to all sheets in workbook | Excel Worksheet Functions | |||
apply a macro to all sheets except for a certain sheet | Excel Discussion (Misc queries) |