Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel VBA Format color of cells changed on sheet change event

Hi
I am using the code given to me in a previous expert post  to change all cells
with the same value as the cell being changed.
How do I also change the formatting of all these changed cells (other than the target cell) to be the same
background color

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
Avatar of Rgonzo1971
Rgonzo1971

Hi,

Which format should the target cell and which format should the remaining column have?

Regards
Avatar of Murray Brown

ASKER

I want the target cell to have a background color of orange and all the other cells to have no background color (ie set back to no color)
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks very much