Get Cells to flash based on a condition

[Panda]

Hi There,


I have a worksheet that records the date and time some PCBs go into an oven (Attached).

I am trying to get the entire row highlighted in yellow if the time in column k is less then the time displayed within column K. (The time in column K is a ticking clock), then get the entire row highlighted in red if the time in column M is less then in column K. However rather then it just being highlighted in red I would like to get the entire row that is highlighted to flash so that it attracts the attention of the operators.

Below is some code that I found on the web that will make the text within a cell flash, can this be modified in any way to get it to do the above?

Thanks in advance

Phil

[GTO]

Hi Panda,

I believe you forgot to include the code in the attached workbook at #1.

[Panda]

opps my bad,

Here is the code that I would have to paste into a new module;

[VBA]Public RunWhen As Double
Sub StartBlink()
With ThisWorkbook.Worksheets("Sheet1").Range("A1:E20").Font
If .ColorIndex = 3 Then ' Red Text
.ColorIndex = 2 ' White 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
Sub StopBlink()
ThisWorkbook.Worksheets("Sheet1").Range("A1:E20").Font.ColorIndex = _
xlColorIndexAutomatic
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", , False
End Sub[/VBA]



and below is the code that I have to paste into the 'This Workbook' module

Thanks

Phil

[Kenneth Hobs]

The most flashing that I would ever recommend would be in a statusbar and limit that. It is not generally considered a good thing to flash font color or cell interior colors. I guess if you are the only user and no other people would see your screen then that would be ok. If you want to continue, you might include your ThisWorkbook code.

Please click the VBA button and paste code or type the code tags (vba) (/vba) , replace ()'s with []'s, and paste code between tags to make code more readable.

If you want to try a statusbar message, here is an example with an option to also tell the user the message.

[vba]Sub Test_SayAndFlashStatusBar()
SayAndFlashStatusBar "Hello World!"
End Sub

Sub SayAndFlashStatusBar(msg As String, Optional Say As Boolean = True, Optional nLoops As Integer = 3)
Dim i As Integer
For i = 1 To nLoops
With Application
.StatusBar = msg
.Wait (Now() + TimeValue("00:00:03"))
.StatusBar = False
.Wait (Now() + 1.15740740740741E-05)
If Say Then .Speech.Speak msg
End With
Next i
End Sub[/vba]

[Panda]

Hi Kenneth,

Sorry I seem to be having a mare today, below is the code that is in the 'This Workbook' module;

[vba]
Private Sub Workbook_Open()
StartBlink
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopBlink
End Sub

[/VBA]

I have been asked by my manager to make the cells flash so that it grabs his attention when his is looking at it on his PC in his office so I guess he is not going to make this sheet 'public'

Thanks

Phil

[Kenneth Hobs]

I will look at this later today. I think that I understand but can you re-explain what you want? Column K is the User Stamp, not one of the time columns L or M. When you say highlighted, do you mean interior color or font color?

For the font color to flash in E2 and E10:E20, the code you posted would need the sheet name changed as shown below.

[VBA]Public RunWhen As Double
Sub StartBlink()
With ThisWorkbook.Worksheets("Live_Screen").Range("A1:E20").Font
If .ColorIndex = 3 Then ' Red Text
.ColorIndex = 2 ' White 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

Sub StopBlink()
ThisWorkbook.Worksheets("Live_Screen").Range("A1:E20").Font.ColorIndex = _
xlColorIndexAutomatic
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", , False
End Sub[/VBA]

[Panda]

I will look at this later today. I think that I understand but can you re-explain what you want? Column K is the User Stamp, not one of the time columns L or M. When you say highlighted, do you mean interior color or font color?

For the font color to flash in E2 and E10:E20, the code you posted would need the sheet name changed as shown below.

[vba]Public RunWhen As Double
Sub StartBlink()
With ThisWorkbook.Worksheets("Live_Screen").Range("A1:E20").Font
If .ColorIndex = 3 Then ' Red Text
.ColorIndex = 2 ' White 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

Sub StopBlink()
ThisWorkbook.Worksheets("Live_Screen").Range("A1:E20").Font.ColorIndex = _
xlColorIndexAutomatic
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", , False
End Sub[/vba]

Thanks Kennith,

basically if the pcb has surpassed the minumum time it is required to be in the oven, then colour the cell yellow, but if it has surpassed the max time it should be in the oven colour the cell red and flash to grab my managers attention.

The time in K2 is a ticking clock (for effect)

Thanks again for taking the time to look at this for me

Phil

[Panda]

Bump....can anyone help?

[Kenneth Hobs]

It took me a bit to get back to this. To make this work, format your J2 to be the same format as column B dates. Set that date to 04/03/2011 to get a mix of the various formats for the rows. You should put some buttons to stop and start this as it is very cpu intensive.

[vba]Public RunWhen As Double

Sub StartBlink()
Dim dataColBRange As Range, cell As Range, row As Long
Dim curDT As Double, minDT As Double, maxDT As Double

'Current Date Time Value
curDT = CLng(CDate(Range("j2").Value)) + CDbl(CDate(Range("k2").Value))

Set dataColBRange = ThisWorkbook.Worksheets("Live_Screen").Range("B11:B" & _
ThisWorkbook.Worksheets("Live_Screen").Range("B11").End(xlDown).row)

For Each cell In dataColBRange
If Range(cell, cell.Offset(0, 9)).Font.ColorIndex = 3 Then
Range(cell, cell.Offset(0, 9)).Font.ColorIndex = 2 'white to make it flash
End If
Next cell

For Each cell In dataColBRange
minDT = CLng(CDate(cell.Value)) + CDbl(CDate(Range("H" & cell.row).Value))
maxDT = CLng(CDate(cell.Value)) + CDbl(CDate(Range("H" & cell.row).Value))
Select Case True
Case curDT - minDT < CDbl(CDate(Range("H" & cell.row).Value))
Range(cell, cell.Offset(0, 9)).Interior.ColorIndex = xlColorIndexAutomatic
Range(cell, cell.Offset(0, 9)).Font.ColorIndex = 1 'black
Case (curDT - minDT >= CDbl(CDate(Range("H" & cell.row).Value))) And _
(curDT - maxDT < CDbl(CDate(Range("I" & cell.row).Value)))
Range(cell, cell.Offset(0, 9)).Interior.ColorIndex = 6 'Yellow
Range(cell, cell.Offset(0, 9)).Font.ColorIndex = 1 'black
Case curDT - maxDT >= CDbl(CDate(Range("I" & cell.row).Value))
Range(cell, cell.Offset(0, 9)).Interior.ColorIndex = xlColorIndexAutomatic
Range(cell, cell.Offset(0, 9)).Font.ColorIndex = 3 'red
End Select
Next cell

RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", , True
End Sub

Sub StopBlink()
ThisWorkbook.Worksheets("Live_Screen").Range("B11:K" & _
ThisWorkbook.Worksheets("Live_Screen").Range("B11").End(xlDown).row).Font.C olorIndex = _
xlColorIndexAutomatic

ThisWorkbook.Worksheets("Live_Screen").Range("B11:K" & _
ThisWorkbook.Worksheets("Live_Screen").Range("B11").End(xlDown).row).Interi or.ColorIndex = _
xlColorIndexAutomatic
Application.OnTime RunWhen, "'" & ThisWorkbook.Name & "'!StartBlink", , False
End Sub[/vba]

[Panda]

Thanks for your time Kenneth, I will give that a go when I am back in the office. =