Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Default Cell Change Color - Need Help

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.
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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


--
HTH

Bob Phillips

"alani" wrote in message
...

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.


--
alani



  #3   Report Post  
Junior Member
 
Posts: 2
Default

Hi Bob,

Thanks for the quick reply. I've tried it but it didn't work as expected or maybe i did something wrong. I've already click and paste the script as instructed but no change. I've attached a screenshot of what I intend to do. For column A will be the timer which will be updating according to my earlier description. Column B is the System name and column C is the Status which I need help.

Thanks again Bob.
  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default

Not sure Alani.

Why don't you send me your workbook direct, and I will install it.

--
HTH

Bob Phillips

"alani" wrote in message
...

Hi Bob,

Thanks for the quick reply. I've tried it but it didn't work as
expected or maybe i did something wrong. I've already click and paste
the script as instructed but no change. I've attached a screenshot of
what I intend to do. For column A will be the timer which will be
updating according to my earlier description. Column B is the System
name and column C is the Status which I need help.

Thanks again Bob.


--
alani



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
text color change in cell reference TomKat743 Excel Discussion (Misc queries) 3 June 16th 05 12:38 AM
up to 7 functions? ALex Excel Worksheet Functions 10 April 12th 05 06:42 PM
Look for change next blank cell in Range Nigel Bennett Excel Worksheet Functions 1 March 13th 05 10:45 PM
change a function in a workbook without changing every cell sir lancelot Excel Worksheet Functions 3 March 11th 05 12:29 AM
Change workbook sheet reference using cell A1 to change a vairable Reed Excel Worksheet Functions 4 January 20th 05 08:15 PM


All times are GMT +1. The time now is 08:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"