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

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

--
Message posted from http://www.ExcelForum.com

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

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

--
Regards
Frank Kabel
Frankfurt, Germany


i have a worksheet that shows worktimes for employees, the cells are
grey, when you enter the employees time or "shift" and press a button
that executes my macro, it changes the colour of the "shift" to white
to show the time scale that the employee is working. the macro runs
fine, the problem is that to run the macro i have to click a button.
is there a way of running the macro, or executing the code when i
enter 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
on 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.


---
Message posted from http://www.ExcelForum.com/

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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frank Kabel" wrote in message ...
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

--
Regards
Frank Kabel
Frankfurt, Germany


i have a worksheet that shows worktimes for employees, the cells are
grey, when you enter the employees time or "shift" and press a button
that executes my macro, it changes the colour of the "shift" to white
to show the time scale that the employee is working. the macro runs
fine, the problem is that to run the macro i have to click a button.
is there a way of running the macro, or executing the code when i
enter 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
on 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.


---
Message posted from http://www.ExcelForum.com/

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

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)

"sparky3883 " wrote in message
...
i have a worksheet that shows worktimes for employees, the cells are
grey, when you enter the employees time or "shift" and press a button
that executes my macro, it changes the colour of the "shift" to white
to show the time scale that the employee is working. the macro runs
fine, the problem is that to run the macro i have to click a button. is
there a way of running the macro, or executing the code when i enter
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 on
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.


---
Message posted from http://www.ExcelForum.com/

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

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


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
is there a way of running the macro, or executing the code when
enter 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 clic
on a button
Private Function ShadeCells(
Dim rng As Range, cell As Rang
Range("D8:AJ106").Interior.ColorIndex = 1
Set rng = Range("B8:AJ106"
For Each cell In rn
Cells.ShrinkToFit = Tru
Select Case cell.tex
Case "6~10
Range("D" & cell.row).Resize(1, 8).Interior.ColorIndex =
Case "6~11
Range("D" & cell.row).Resize(1, 10).Interior.ColorIndex =
Case "6~12
Range("D" & cell.row).Resize(1, 12).Interior.ColorIndex =
Case "6~3
Range("D" & cell.row).Resize(1, 18).Interior.ColorIndex =
Case "7~4
Range("F" & cell.row).Resize(1, 18).Interior.ColorIndex =
Case "E
Range("I" & cell.row).Resize(1, 18).Interior.ColorIndex =
Case "8~5
Range("H" & cell.row).Resize(1, 18).Interior.ColorIndex =
Case "8.30~5.30
Range("I" & cell.row).Resize(1, 18).Interior.ColorIndex =
Case "9~6
Range("J" & cell.row).Resize(1, 18).Interior.ColorIndex =
End Selec
Next cel
End Functio
thanks in advance for anything
--
Message posted from http://www.ExcelForum.com