Private Sub Worksheet_Change(ByVal Target As Range)

[Suzannah Robertson]

Hello,

I'm trying to use a Private Sub Worksheet_Change(ByVal Target As Range) macro on one of my sheets and it's not working.
I've put in the code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Partner
If Target.Row = 8 Then
thiscolumn = Target.Column

Range("e71").select...

and it's not triggered when I change information on row 8 of the worksheet... Can anyone see what I'm doing wrong?

Any help much appreciated.

thanks,
Suzannah

 

[ZVI]

Glad you worked it out, Suzannah!

You can use this testing code for debugging:

Private Sub Worksheet_Change(ByVal Target As Range)
  ' Code lines with MsgBox and Activate in it are for debugging only
  ' comment/delete such lines after debugging
  MsgBox "Worksheet_Change starts"
  Dim x As Range
  With Sheets("utilisation")
    .Activate
    For Each x In .Range("E71:E1000")
      If UCase(x.Value) = "E" Then Exit For
      If x <> 0 And x.HasFormula Then
        x.Activate
        MsgBox "GoalSeek for " & x.Address(0, 0) & vbLf & "ChangingCell " & x.Offset(-19, 52).Address(0, 0)
        x.GoalSeek Goal:=0, ChangingCell:=x.Offset(-19, 52)
        MsgBox IIf(x = 0, "Goal is achieved", "Unsuccessful")
      End If
    Next
  End With
  MsgBox "Worksheet_Change ends"    '  <-- for debugging only
End Sub

And this is for working.
It's the same as above but without debugging lines:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim x As Range
  With Sheets("utilisation")
    For Each x In .Range("E71:E1000")
      If UCase(x.Value) = "E" Then Exit For
      If x <> 0 And x.HasFormula Then
        x.GoalSeek Goal:=0, ChangingCell:=x.Offset(-19, 52)
      End If
    Next
  End With
End Sub

Regards

 

[Suzannah Robertson]

thanks ZVI