Changing color of a cell every second autmatically

[mine0103]

How can I accomplish changing color of a cell every second automatically

 

[Gary's Student]

This code is based on Chip Pearson's example:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1
Public Const cRunWhat = "refresh"

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
        schedule:=True
End Sub

Sub StopTimer()
  On Error Resume Next
  Application.OnTime earliesttime:=RunWhen, _
      procedure:=cRunWhat, schedule:=False
End Sub

Sub refresh()
    With Application.WorksheetFunction
        Range("B2").Interior.ColorIndex = .RandBetween(1, 56)
    End With
    Call StartTimer
End Sub

Run StartTimer to begin and StopTimer to end.

 

[Hui]

You will need to use some VBA code to update a cell every second with say True/False
Then use that cell to control the CF

I'd strongly recommend against it as you are going to place a load on your PC and the change may trigger all Volatile Functions to recalculate

 

[mine0103]

This code is based on Chip Pearson's example:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1
Public Const cRunWhat = "refresh"

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
        schedule:=True
End Sub

Sub StopTimer()
  On Error Resume Next
  Application.OnTime earliesttime:=RunWhen, _
      procedure:=cRunWhat, schedule:=False
End Sub

Sub refresh()
    With Application.WorksheetFunction
        Range("B2").Interior.ColorIndex = .RandBetween(1, 56)
    End With
    Call StartTimer
End Sub

Run StartTimer to begin and StopTimer to end.

Hello Gary's Student,
Thank you so much. I checked the code and it is working exactly the way I expected. Can you also please advice me how can I accomplish running a macro automatically when I start an excel workbook?