event if cell is changed - but only if the value is different from original value

[tomleitch]

Hi all,

I have some vba running on my sheet that colour fills a cell if it is changed.

Current code is this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A5:F2000")) Is Nothing Then Target.Interior.Color = rgbBeige
End Sub


However - I am pasting values into cells with another macro - and I only want the cells to be colour filled if the pasted value is different from the value that was in the cells in the first place.


Thanks
Tom

 

[dmt32]

Hi,
To do what you want you will need a place holder to store the cell values to make the comparison with.
One possible way to achieve this would be to use the Range.ID property

Example

 Private Sub Worksheet_Change(ByVal Target As Range)    
If Not Intersect(Target, Range("A5:F2000")) Is Nothing Then
        With Target
        If .ID <> .Value Then
            .Interior.Color = rgbBeige
            .ID = .Value
        Else
            .Interior.Color = xlNone
        End If
        End With
    End If
End Sub

However, Range.ID does not (I think) retain its value when workbook is saved & closed so you may need to initialize them when workbook is opened

Example

 Private Sub Workbook_Open()    Dim rng As Range, cell As Range
    Set rng = Worksheets("Sheet1").Range("A5:F2000")
    
    For Each cell In rng.Cells
        cell.ID = cell.Value
    Next cell
End Sub

This is just an idea & you will need to adapt solution to meet specific project need as required

Others here may have alternative solutions.

Dave

 

[Fluff]

Does the code I supplied here https://www.mrexcel.com/forum/excel...n-two-sheets-cutting-pasting.html#post5018404 not work as needed?

 

[tomleitch]

thanks for the help Dave, I'll try it

 

[dmt32]

thanks for the help Dave, I'll try it

welcome - do let us know how get on

Dave