getting a macro to run when a change is made to a specific row

[sparky3883]

i have a worksheet that shows worktimes for employees, the cells ar
grey, when you enter the employees time or "shift" and press a butto
that executes my macro, it changes the colour of the "shift" to whit
to show the time scale that the employee is working. the macro run
fine, the problem is that to run the macro i have to click a button. i
there a way of running the macro, or executing the code when i ente
the employees "shift" of time. or is this not possible?

this is the code i am using at the minute for it to run when i click o
a button:

Private Function ShadeCells()
Dim rng As Range, cell As Range
Range("D8:AJ106").Interior.ColorIndex = 15
Set rng = Range("B8:AJ106")
For Each cell In rng
Cells.ShrinkToFit = True
Select Case cell.text
Case "6~10"
Range("D" & cell.row).Resize(1, 8).Interior.ColorIndex = 0
Case "6~11"
Range("D" & cell.row).Resize(1, 10).Interior.ColorIndex = 0
Case "6~12"
Range("D" & cell.row).Resize(1, 12).Interior.ColorIndex = 0
Case "6~3"
Range("D" & cell.row).Resize(1, 18).Interior.ColorIndex = 0
Case "7~4"
Range("F" & cell.row).Resize(1, 18).Interior.ColorIndex = 0
Case "E"
Range("I" & cell.row).Resize(1, 18).Interior.ColorIndex = 0
Case "8~5"
Range("H" & cell.row).Resize(1, 18).Interior.ColorIndex = 0
Case "8.30~5.30"
Range("I" & cell.row).Resize(1, 18).Interior.ColorIndex = 0
Case "9~6"
Range("J" & cell.row).Resize(1, 18).Interior.ColorIndex = 0

End Select
Next cell
End Function

thanks in advance for anything

 

[Frank Kabel]

Hi
you may use an event procedure for this. See the following site for
more details (in your case the worksheet_change event should do):
http://www.cpearson.com/excel/events.htm

 

[Ron de Bruin]

You can use this event for example like frank told you

If Target.Row = 3
This will only run the macro if you change a cell in row 3


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 3 Then
yourmacroname
End If
End Sub

Sub yourmacroname()
MsgBox ""
End Sub

 

[Bob Phillips]

Put it in worksheet change event code. I have assumed that a change to
column E triggers this code but this could be changed

Private Sub Worksheet_hange(ByVal Target As Range)
Dim cell As Range

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Targte, Columns(5)) Is Nothing Then
Range("D8:AJ106").Interior.ColorIndex = 15
Cells.ShrinkToFit = True
For Each cell In Range("B8:AJ106")
With cell
Select Case .Text
Case "6~10"
Range("D" & cell.Row). _
Resize(1, 8).Interior.ColorIndex = 0
Case "6~11"
Range("D" & cell.Row). _
Resize(1, 10).Interior.ColorIndex = 0
Case "6~12"
Range("D" & cell.Row). _
Resize(1, 12).Interior.ColorIndex = 0
Case "6~3"
Range("D" & cell.Row). _
Resize(1, 18).Interior.ColorIndex = 0
Case "7~4"
Range("F" & cell.Row). _
Resize(1, 18).Interior.ColorIndex = 0
Case "E"
Range("I" & cell.Row). _
Resize(1, 18).Interior.ColorIndex = 0
Case "8~5"
Range("H" & cell.Row). _
Resize(1, 18).Interior.ColorIndex = 0
Case "8.30~5.30"
Range("I" & cell.Row). _
Resize(1, 18).Interior.ColorIndex = 0
Case "9~6"
Range("J" & cell.Row). _
Resize(1, 18).Interior.ColorIndex = 0
End Select
Next cell
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[chris]

Do this
in the Projects window go to ThisWorkbook. under the sheetchange event write

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) " this part is generated for you
if selection.row = X (where x is the row number that activates the change) the
" your code here
end i
End Su


----- Frank Kabel wrote: ----

H
you may use an event procedure for this. See the following site fo
more details (in your case the worksheet_change event should do)
http://www.cpearson.com/excel/events.ht

-
Regard
Frank Kabe
Frankfurt, German