Search code examples
excelexcel-2007vba

Highlighting modified cell


I was trying to highlight a cell in a column which is modified from "a" to "b" (should exclude the changes from blank to "a") via macro. Can anyone help me?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
Dim KeyCells As Range
Set KeyCells = Range("A:A")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
ActiveCell.Select
Application.Run ("color")
End If
End Sub

Sub color()
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .color = 255
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
End Sub

The above code highlights the cells below the edited cell also.


Solution

  • Dim ValBeforeChange As String
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
        ValBeforeChange = Target.Value
    
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Application.ScreenUpdating = False
    
        
    
        If ValBeforeChange ="" Then
            Exit Sub
        End If
        IF ValBeforeChange <> Target.Value Then
        
        If Application.Intersect(Range("A:A"), Target) Is Nothing Then
        Application.Run("Color")    
        End If
      
        End If
        
    
    End Sub

    This code works well guys thank you all for your help :)