• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 732
  • Last Modified:

Excel VBA change all other values in column in Worksheet_Change

Hi

Is it possible in Excel VBA in the Worksheet_Change event to change all cells with the same original value  in the target column to the value the cell is changed to
0
Murray Brown
Asked:
Murray Brown
1 Solution
 
Wayne Taylor (webtubbs)Commented:
You'll need to use the SelectionChange event also to capture what the previous value was.

This code will only affect column B...

Private PrevValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then 'column B
        Application.EnableEvents = False
        Target.EntireColumn.Replace PrevValue, Target.Value, xlWhole
        PrevValue = Target.Value
        Application.EnableEvents = True
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PrevValue = Target.Value
End Sub

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now