code modification

Kim B

Board Regular
Joined
Jun 16, 2008
Messages
233
Office Version
  1. 365
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top