Alert

[Sathisc]

Hi,

I have a data in which i enter time in a cell. I need a macro like when
the cell's time is exceeding 45 mins form the system time the entire row
should change into red. But when any data is entered in Cell E1 the
alert needs to stop.

M.No Date Time Place Outcome
3059934009 7/5/2009 15.03 P0 5LX

In the above example the Time 15:03 is entered at real time i need an
alert or the entire cell turns into red when the system time is 15:48
exactly after 45 mins of the job time(15:03). If the outcome data is
filled in then the alert should get stop.

Many thanks for the help

 

[Simon Lloyd]

Hi,

I have a data in which i enter time in a cell. I need a macro like when
the cell's time is exceeding 45 mins form the system time the entire row
should change into red. But when any data is entered in Cell E1 the
alert needs to stop.

M.No Date Time Place Outcome
3059934009 7/5/2009 15.03 P0 5LX

In the above example the Time 15:03 is entered at real time i need an
alert or the entire cell turns into red when the system time is 15:48
exactly after 45 mins of the job time(15:03). If the outcome data is
filled in then the alert should get stop.

Many thanks for the helpYou would need to use the ontime method, starting the timer when the

cell becomes active and ending the timer if the cell beomes filled or
the row coloured red.

It's not an exact science as the timer will restart should you move
cells even if data hasn't been entered previously!


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Sathisc]

Hi Simon,

I am a beginner in Excel. Can you breif more on the ontime method.

Cheers,
Sathish

 

[Simon Lloyd]

Hi Simon,

I am a beginner in Excel. Can you breif more on the ontime method.

Cheers,
SathishResearch the OnTime method, there's lots of info out there, however

follow these instructions and you should be good to go:
Paste this code in the worksheet code module that you are working with

*How to Save a Worksheet Event Macro*
1. *Copy* the macro using *CTRL+C* keys.
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab*
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r2 As String
r2 = Range("IV" & Range(MyRange).Row).End(xlToLeft).Address
If Range(MyRange) <> "" Then
Range(MyRange & ":" & r2).Interior.ColorIndex = xlNone
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MyRange = Target.Address
If Target = "" Then GoTo Nxt
If Target <> "" Then
Range(MyRange & ":" & Range("IV" & Range(MyRange).Row).End(xlToLeft).Address).Interior.ColorIndex = xlNone
End If
Exit Sub
Nxt:
'in this timeserial 0 = hrs, 0 = minutes, 0 = seconds it is set for 10 seconds at the moment
Application.OnTime (Now + TimeSerial(0, 0, 10)), "ColourRow"
Exit Sub
End Sub
--------------------
Next paste this in a standard code module:

*How to add and run a Macro*1. *Copy* the macro below pressing
the keys *CTRL+C*
2. Open your workbook
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

*To Run the Macro...*
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to *Run*
it.



Code:
--------------------
Public MyRange As String
Sub ColourRow()
Range(Activecell.Address & ":" & Range("IV" & Activecell.Row).End(xlToLeft).Address) _
.Interior.ColorIndex = 3
End Sub

--------------------
The code should now do what you want, although i have set it to 10
seconds so you can see it working!


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)