Hi all, I took this code from another spreadsheet and need it simplified. I was able to get the cell to change color, but not blink. I am wondering if it is because the code refers to indexes. Anyway, my code would be much more simple.
Here is the data. 1 is good 5 is bad. So the code would result in a 5 blinking. The values in the cells are colored, but it is the number that is most important.
[TABLE="width: 206"]
<tbody>[TR]
[TD]Building and grounds[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Pumps[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Motors[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Generator[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Scada Panel[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Electrical Panel[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Control Panel[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Flow meter[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Odor Control System[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Channel Grinder[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
Here is the code
Option Explicit
'In a regular module sheet
Public RunWhen As Double 'This statement must go at top of all subs and functions
Sub StartBlink()
Dim cel As Range
With ThisWorkbook.Worksheets("Sheet1")
Set cel = .Range("A7")
If cel.Value > .Range("A1").Value Then
If cel.Font.ColorIndex = 3 Then ' Red Text
cel.Font.ColorIndex = 2 ' White Text
cel.Interior.ColorIndex = 3
Else
cel.Font.ColorIndex = 3 ' Red Text
cel.Interior.ColorIndex = xlColorIndexAutomatic
End If
Else
cel.Font.ColorIndex = 3 'Red text
cel.Interior.ColorIndex = xlColorIndexAutomatic
End If
End With
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", , True
End Sub
Sub StopBlink()
On Error Resume Next
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", , False
On Error GoTo 0
With ThisWorkbook.Worksheets("Sheet1")
.Range("A7").Font.ColorIndex = 3
.Range("A7").Interior.ColorIndex = xlColorIndexAutomatic
End With
End Sub
Sub xStopBlink()
On Error Resume Next
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", , False
On Error GoTo 0
ThisWorkbook.Worksheets("Sheet1").Range("A1").Font.ColorIndex = 3
End Sub
Thank you
Here is the data. 1 is good 5 is bad. So the code would result in a 5 blinking. The values in the cells are colored, but it is the number that is most important.
[TABLE="width: 206"]
<tbody>[TR]
[TD]Building and grounds[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Pumps[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Motors[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Generator[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Scada Panel[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Electrical Panel[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Control Panel[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Flow meter[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Odor Control System[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Channel Grinder[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
Here is the code
Option Explicit
'In a regular module sheet
Public RunWhen As Double 'This statement must go at top of all subs and functions
Sub StartBlink()
Dim cel As Range
With ThisWorkbook.Worksheets("Sheet1")
Set cel = .Range("A7")
If cel.Value > .Range("A1").Value Then
If cel.Font.ColorIndex = 3 Then ' Red Text
cel.Font.ColorIndex = 2 ' White Text
cel.Interior.ColorIndex = 3
Else
cel.Font.ColorIndex = 3 ' Red Text
cel.Interior.ColorIndex = xlColorIndexAutomatic
End If
Else
cel.Font.ColorIndex = 3 'Red text
cel.Interior.ColorIndex = xlColorIndexAutomatic
End If
End With
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", , True
End Sub
Sub StopBlink()
On Error Resume Next
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", , False
On Error GoTo 0
With ThisWorkbook.Worksheets("Sheet1")
.Range("A7").Font.ColorIndex = 3
.Range("A7").Interior.ColorIndex = xlColorIndexAutomatic
End With
End Sub
Sub xStopBlink()
On Error Resume Next
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", , False
On Error GoTo 0
ThisWorkbook.Worksheets("Sheet1").Range("A1").Font.ColorIndex = 3
End Sub
Thank you