Running macros when cell changes

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:53 PM GMT

Question

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C5")) Is Nothing Then 
 Exit Sub 
Else 
 'The cell you are monitoring has changed! 
 Msgbox (Target.Address)
End If 

End Sub

This displays the message box as soon as the cell C5 is SELECTED, even though all websites cite this as a method for running a macro when the cell is CHANGED. Am I misinterpreting something?

The Microsoft web site has this macro which seems to be more appropriate for AFTER something is entered in a cell (AFTER hitting Enter). Can you pl tell me if I have understood correctly, or clear my misunderstanding if not?

Sub auto_open()

  ' Run the macro DidCellsChange any time a entry is made in a
  ' cell in Sheet1.
  ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"

End Sub


Sub DidCellsChange()

 Dim KeyCells As String
  ' Define which cells should trigger the KeyCellsChanged macro.
  KeyCells = "A1:A10, B1:B10, C1:C10"
  ' If the Activecell is one of the key cells, call the
  ' KeyCellsChanged macro.
  If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
  Is Nothing Then KeyCellsChanged

End Sub

Sub KeyCellsChanged()

  Dim Cell As Object
  ' If the values in A11:C11 are greater than 50...
  For Each Cell In Range("A11:C11")
  If Cell > 50 Then
  ' Make the background color of the cell the 3rd color on the
  ' current palette.
  Cell.Interior.ColorIndex = 3
  Else
  ' Otherwise, set the background to none (default).
  Cell.Interior.ColorIndex = xlNone
  End If
  Next Cell

End Sub

Answer

Open a new blankworkbook. In the Visual Basic Editor, in the Sheet1 object, enter the vba code you include in your question:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C5")) Is Nothing Then
          Exit Sub
    Else
          Msgbox (Target.Address)
    End If

End Sub

Then on Sheet1, select cell C5. The message will NOT appear. Then change the value in cell C5. The message WILL appear. If this isn't happening for you, you must have some other macro running. When you have no vba procedures except the one you included in your question, it will work exactly as you say it should.

Advertisement

©2021 eLuminary LLC. All rights reserved.