Solved

Excel VBA change all other values in column in Worksheet_Change

Posted on 2014-10-27
2
592 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 40
Excel 2016 Not Responding Issues 6 27
Move entire row to another workbook based on cell value 20 45
Vlookup formula error 15 0
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now