Automaticlly Running Macro on Changed Cell

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have the following Module programmed in Excel and it
does exactly what I need it to do except run when ever a
cell is updated.

I need it to run whenever B9 -> B60 is changed or E-> E60
is changed. What do I need to do?

-Chris

Sub CheckStatus()
Dim H33, H34, Status1, Status2

H33 = Cells(33, 8).Value
H34 = Cells(34, 8).Value

Select Case H33
Case 7
Status1 = "Verbal"
Case 8
Status1 = "Written"
Case 9
Status1 = "Termination"
Case Else
Status1 = ""
End Select

Select Case H34
Case 7
Status2 = "Verbal"
Case 8 To 11
Status2 = "Written"
Case 12
Status2 = "Termination"
Case Else
Status2 = ""
End Select
If H33 > H34 Then
Range("H36").Value = Status1
Else
Range("H36").Value = Status2
End If
End Sub
 
Right click on the worksheet tab and select view code. Paste in code like
the below into the Sheet module which is presented.

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count > 1 then exit sub
if not intersect(Target,Range("B9:B60,E9:E60")) is nothing then
CheckStatus
End if
End Sub
 
Back
Top