Solved

Excel VBA change all other values in column in Worksheet_Change

Posted on 2014-10-27
2
604 Views
Last Modified: 2014-10-27
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
Comment
Question by:murbro
2 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 40407755
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
 

Author Closing Comment

by:murbro
ID: 40407780
Thanks very much
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question