Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Conditional Formatting if another cell is modified

    Is it possible to have conditional formatting applied to a cell if another cell has been modified? For example if the data in A1 has been modified then I would like the data in B1 to be highlighted automatically.

    Thanks!
    Mostate

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    The original value must be stored somewhere as a comparison to the changed value. One way that this can be done is writing the value to a variable then comparing the variable to the cell after modification. If not the same then color the offset cell's interior.

    In the worksheet's module:
    Code:
    Private OldValue
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Count > 1 Then Exit Sub
        If OldValue <> "" And Target.Value <> "" And _
            Target.Value <> OldValue Then _
            Target.Offset(0, 1).Interior.Color = vbRed
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Count > 1 Then Exit Sub
        OldValue = Target.Value
    End Sub
    HTH,
    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •