Run Macro When Cell Value Changes or Range VBA

Call Macro if Cell Value Changes

Learn how to create a VBA Range macro to run when any specific cell value changes. Also we don’t have to explicit use any User interface controls.

Most of the time we map a set of VBA code to a Command button or other User interfaces. These UI controls can be used to start the code execution whenever required.

Sometimes, We might want to run VBA macro code automatically, without explicitly using any UI  (For example, if User clicks on Sheet1, we might want to ask a password from user to view the content or throw a message that the sheet is restricted only to specific user group).

Run Macro when Cell changes to Specific value

Lets assume a specific example to understand how it works.

We need to make a macro to run when anyone edit or change the value in any specific cell (“A1”) in a worksheet. We are going to use the Worksheet change event that is in-built within Excel.

• Create a New Excel Workbook.
• Press Alt+F11 to get to VB Editor.
• Copy Paste below code.
• Executing the code: No hurry in pressing F5.
• Go to Sheet1. Edit the value in Cell A1. This will run macro since value got changed & a message box will be displayed.

More Tips: Add Worksheets Dynamically to Excel During Macro Run time

Method 1 – Modifying Worksheet Change Event to Call macro on Cell change

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        '
        'Enter Code or Call any Function if any process has to be performed
        'When someone Edits the cell A1
        '
        Call VBA_Range
        '
        '
    End If
End Sub

Sub VBA_Range()
    MsgBox "Sub Function is called when cell A1 is edited"
End Sub

Method 2 – Thisworkbook Sheetchange Modified

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "SheetName: " & Sh.Name & ", Cell: " & Target.Address
End Sub

One Level Up – Run VBA for Range of Cells

Instead of a cell, if the you need to run macro when value changes in a column, row or range of cells, then focus on the Target.Address in the above mentioned code. It will return value in any of the below formats.

1. VBA Cell Address: $ <ColumnName> $ <RowNumber> – Example: $A$1 , $AD$100, $X$1000 etc.,
2. VBA Range Address: $ <Column Start> $ <Row Start> : $ <Column End> $ <Row End> – Example: $A$1:$G$100

Assign these values to a temporary variable and use VBA String functions to use in your condition check that will give you a VBA Range macro.

If the code has executed when a column value changes, use ‘ If Target.Column = <ColumnName> Then‘.

To know more properties/options that can be used with Target, type this ‘Excel.Range.’ in VB Editor. It will give the available options that can be used with Target.

More Tips: Create Folder or Directories in Windows From Excel During Macro Run Time