Cell Change Color - Need Help

A

alani

Hi All,

I wonder if anyone can help me on this. I'm working on something which
will help me to monitor inputs. e.g. A system will send a heartbeat
every minute which I will be able capture as input to my excel
spresheet, say cell C3. Now, this are the requirement for the output:

If cell C3 is updating every minute consistently then cell A1 will
remain or change to GREEN.

If cell C3 is not updating after 1minute 30seconds then cell A1 will
change to AMBER but if it is updating again cell A1 will change to
GREEN again.

If cell C3 is not updating after 2minutes the cell A1 will change to
RED but if it is updating again cell A1 will change to GREEN again.

Cell C3 will stop receiving update from 7pm to 7am next day. During
this period, cell A1 will change to GREY.

The whole process repeats itself again excluding Saturday, Sunday and
Public holidays.
 
B

Bob Phillips

Alani,

You could try this.

Put this code in the worksheet code module

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C3")) Is Nothing Then
With Target
.Interior.ColorIndex = 4
End With
End If
Application.OnTime nTime, "TurnGreen", ,False
nTime = Now + TimeSerial(0, 1, 0)
Application.OnTime nTime, "TurnGreen"

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


and this in a standard code module

Public nTime As Double

Public Sub TurnGreen()
nTime = Now + TimeSerial(0, 1, 0)
Range("C3").Interior.ColorIndex = 45
Application.OnTime nTime, "TurnGreen"
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top