CODEWITHSUNDEEP
vbaexcel
geocc
geocc

Reputation: 31

Capture a row number before change VBA

I just started writing VBA code and I am facing a problem. The code works without any problem if i.e. I am in cell b11 and switch to cell a2 or c2. But if I write something in cell b2 and press Enter, then the code doesn't work. I assume this happens because of ActiveCell.row. How can I make it happen to work even when I press Enter?

P.S. I need the ActiveCell.Row so i can get the cell number in the MsgBox error.

Sub Change()

Dim i As Long

i = ActiveCell.Row

If (Cells(i, "B") + Cells(i, "D")) <> Cells(i, "F") Then
    MsgBox "B" & i & " + D" & i & " must equal cell F" & i & " which is: " & Range("W" & i).Value

    Cells(i, "B").Interior.Color = RGB(255, 0, 0)
    Cells(i, "D").Interior.Color = RGB(255, 0, 0)
Else
    Range(Cells(i, "B"), Cells(i, "D")).Interior.Color = RGB(1000, 1000, 1000)
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B11:F10000"), Range(Target.Address)) Is Nothing Then
   Call Change
End If

Upvotes: 1

Views: 936

Answers (2)

user2261597
user2261597

Reputation:

Please start over and make sure your event handler works as it should. Add this in an empty worksheet to try. 

Public Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "You just changed " & Target.Address
End Sub

Note that this event handler MUST reside in this sheet's private module:

enter image description here

More info here: http://www.ozgrid.com/VBA/run-macros-change.htm

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

If you want the code to work when you modify cell "B2", you need to modify your Range in the scanned range in Worksheet_Change event.

Code

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("B11:F10000"), Target) Is Nothing Then
   Change Target.Row ' call change and pass the row number
End If

End Sub

Sub Change(i As Long)
' get the row number directly from the worksheet_change event

If (Cells(i, "B") + Cells(i, "D")) <> Cells(i, "F") Then
    MsgBox "B" & i & " + D" & i & " must equal cell F" & i & " which is: " & Range("W" & i).Value

    Cells(i, "B").Interior.Color = RGB(255, 0, 0)
    Cells(i, "D").Interior.Color = RGB(255, 0, 0)
Else
    Range(Cells(i, "B"), Cells(i, "D")).Interior.Color = RGB(1000, 1000, 1000)
End If

End Sub

Upvotes: 1

Related Questions