Excel VBA Check When Cell Has Changed

anucool · February 28th, 2013, 04:52 AM

Facing the same problem.... not able to automatically run my code on the cell value change.....

PLZ help....

Below is my code

Code:

 
Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Dim IntersectRange As Range
    Set WatchRange = Range("A2")
    Set IntersectRange = Intersect(Target, WatchRange)
    If IntersectRange Is Nothing Then
        'Do Nothing
    Else
        Call kri
    End If
End Sub
Sub kri()
   If Range("A2").Value <> "" Then
  x = Range("A2").Value
  y = (Asc(x) + 12) Mod 128
  If y < 32 Then
    y = y + 32
  End If
  Range("B2").Value = Chr(y)
 End If
End Sub

anucool · February 28th, 2013, 05:02 AM

I have put private for sub worksheet_change
and also enabled macros for my worksheet.... still not working

Shasur · March 3rd, 2013, 10:04 PM

Quote:

Originally Posted by anucool

I have put private for sub worksheet_change
and also enabled macros for my worksheet.... still not working

Can you explain if the Worksheet_Change is not getting fired or the Intersect portion is not executed.

I hope you have placed the code in the Sheet where it is intended (e.g., Sheet1 )

Cheers
Shasur

kambeeks · March 24th, 2015, 05:15 PM

When you are trying to find the cell that you are changing, make sure that you are referencing Target as being your changing cell as opposed to ActiveCell. If you use ActiveCell, it is going to be the ActiveCell after you return the command or tab to the next cell.

ActiveCell.Row when using the Return key will be (1) row below you Target Cell
ActiveCell.Column when using the Tab key will be (1) column to the right of your Target Cell

Use Target.Row or Target.Column
It is already dimensioned as a Range, and it will give you the correct information of the cell that changed in your WatchRange.

Quote:

Originally Posted by tonyrosen

Figured it out ... I was "finding" the wrong cells. My "active" cell was REALLY the desired "active" cell plus one ... Here is the piece I fixed:

    MyRow = ActiveCell.Row
    'This is the Stop Date Column Number
    MyColumn = ActiveCell.Column - 1
    'This is the Start Date Column Number
    MyColumnPrev = MyColumn - 2