title: Conditionally formatting ranges
body: <p>I have two ranges of data that I want to compare with and format if they match. So I want to format a range 1  cell if any of that data matches to the the data in range 2. This is what I have so far - it works until I change the data to range 2 but doesn't update it:</p>
  
  <pre><code>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
  Dim myRange As Range, cell As Range
  Set myRange = Range("a9:a12")
  
  For Each cell In myRange
  If cell.Value = ActiveCell.Value And Not IsEmpty(ActiveCell.Value) Then
  ActiveCell.Interior.ColorIndex = 3
  
  End If
  Next cell
  End Sub
  </code></pre>
  
  <p>The problem is the cell still stays the colors that it was formatted from the first block of code so how can I change it back if the data in the second range gets changed?</p>
  
  <pre><code>  Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRange1 As Range
    Set myRange1 = Range("f9:f12")
  
    If Not Intersect(Target, Range("f1:f6")) Is Nothing Then
    If Application.WorksheetFunction.CountIf(myRange1,   ActiveCell.Value) &gt; 0 _
    Then ActiveCell.Interior.ColorIndex = 3 Else ActiveCell.Interior.Color =   xlNone
    End If
  End Sub
  </code></pre>