ExcelBanter - View Single Post - getting a macro to run when a change is made to a specific row

Bob Phillips[_6_]

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/