problem with macro

[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

 

[markymark]

Yes. This is possible. What you're interested in doing
is called event handling for a change in a cell in a
worksheet range. The event handling code needs to be
placed in the code module for the object (i.e. Sheet1)
that you want the event checked on. While you're at it,
you might as well put the rest of the code you listed in
that same spot as well to specify what to do when the
event your looking for has occured. Regardless, you'll
want to write your code as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("C3102")
If Not Intersect(Target, VRange) Is Nothing Then
(put your macro code here for what you want to
run whenever a change is made to whatever you
have set VRange to be above. I this case, I've
set VRange to be "C3102".)
End If
End Sub

I obtained this technique from John Walkenbach's Excel
2002 Power Programming with VBA book, and I've used it
before in a couple of spreadsheets I've developed. This
is a very good book that I recommend that you purchase if
you want to learn a lot of powerful programming
techniques in VBA.

Good luck,

MarkyMark