Sathisc;337322 Wrote:
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).Interio r.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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94274