title: Excel VBA Macro Conditional Formatting By Referencing Cell Pair Relative Location
body: <p>I am trying to use conditional formatting to highlight a row of cells containing key value pairs in another column when certain watch cells are yellow.  I have a three columns (A,B,C) containing numeric digits and then two columns (key 1, key2) that is also numeric.  Next to the two columns are sensor attribute data that is yellowed under (AB,BC,AC).  My code below is supposed to look at athe attribute cells and see under which columns (AB,BC,AC) are yellow. Then it takes the key pairs (key 1, key2) and finds a match in the three column matrix in terms of values and the relative order of the value in the three columns.  I've been doing this manually and its so much of a pain I need to try to code it but I don't know if its possible.  The problem I have is that the yellowed cells tells the relative order of the key pairs to find the match in the three columns and I do not know how to pull that off.  </p>
  
  <p><img src="https://i972.photobucket.com/albums/ae203/sungate9/ExcelMacro.gif" alt="https://i972.photobucket.com/albums/ae203/sungate9/ExcelMacro.gif"></p>
  
  <p>Sample file here:  <a href="http://www.filefactory.com/file/a0egf75/n/Relative_Position_Macro_xls" rel="nofollow noreferrer">http://www.filefactory.com/file/a0egf75/n/Relative_Position_Macro_xls</a></p>
  
  <pre>
  Key 1   Key 2   AB  BC  AC  AB  BC  AC
  0   0   0.0000  0.0000  0.0000  0.0000  0.0000  0.0000
  0   1   -1.5574 -1.5574 -1.5574 1.5574  1.5574  1.5574
  0   2   2.1850  2.1850  2.1850  -2.1850 -2.1850 -2.1850
  0   3   0.1425  0.1425  0.1425  -0.1425 -0.1425 -0.1425
  0   4   -1.1578 -1.1578 -1.1578 1.1578  1.1578  1.1578
  0   5   3.3805  3.3805  3.3805  -3.3805 -3.3805 -3.3805
  0   6   0.2910  0.2910  0.2910  -0.2910 -0.2910 -0.2910
  0   7   -0.8714 -0.8714 -0.8714 0.8714  0.8714  0.8714
  0   8   6.7997  6.7997  6.7997  -6.7997 -6.7997 -6.7997
  0   9   0.4523  0.4523  0.4523  -0.4523 -0.4523 -0.4523
  1   0   1.5574  1.5574  1.5574  1.5574  1.5574  1.5574
  1   1   0.0000  0.0000  0.0000  -2.1850 -2.1850 -2.1850
  1   2   -1.5574 -1.5574 -1.5574 -0.1425 -0.1425 -0.1425
  1   3   2.1850  2.1850  2.1850  1.1578  1.1578  1.1578
  1   4   0.1425  0.1425  0.1425  -3.3805 -3.3805 -3.3805
  1   5   -1.1578 -1.1578 -1.1578 -0.2910 -0.2910 -0.2910
  1   6   3.3805  3.3805  3.3805  0.8714  0.8714  0.8714
  1   7   0.2910  0.2910  0.2910  -6.7997 -6.7997 -6.7997
  1   8   -0.8714 -0.8714 -0.8714 -0.4523 -0.4523 -0.4523
  1   9   6.7997  6.7997  6.7997  0.6484  0.6484  0.6484
  2   0   -2.1850 -2.1850 -2.1850 -2.1850 -2.1850 -2.1850
  2   1   1.5574  1.5574  1.5574  -0.1425 -0.1425 -0.1425
  2   2   0.0000  0.0000  0.0000  1.1578  1.1578  1.1578
  2   3   -1.5574 -1.5574 -1.5574 -3.3805 -3.3805 -3.3805
  2   4   2.1850  2.1850  2.1850  -0.2910 -0.2910 -0.2910
  2   5   0.1425  0.1425  0.1425  0.8714  0.8714  0.8714
  2   6   -1.1578 -1.1578 -1.1578 -6.7997 -6.7997 -6.7997
  2   7   3.3805  3.3805  3.3805  -0.4523 -0.4523 -0.4523
  2   8   0.2910  0.2910  0.2910  0.6484  0.6484  0.6484
  
  A   B   C
  0   8   9
  0   7   8
  0   6   7
  0   5   6
  0   4   5
  0   7   9
  0   3   4
  0   5   7
  0   2   3
  0   4   6
  0   5   8
  2   1   5
  0   4   7
  0   5   9
  0   4   8
  0   4   9
  0   3   7
  0   2   5
  0   3   9
  0   1   3
  0   2   6
  0   2   7
  0   1   4
  0   2   9
  0   1   5
  0   1   6
  0   1   7
  0   1   8
  0   1   9
  
  </pre>
  
  <p>
  </p>
  
  <p>If anyone can offer me some suggestions, I would really appreciate it.     </p>
  
  <pre><code>Dim WatchRange As Range, Target As Range, cell As Range
  Set WatchRange = Range("C4:H32") 
  Set Target = Range("J4:J32")
  
  For Each cell In WatchRange.Cells
    If ColorIndex: = 6 , A4 = J4, B4 = K4  Then: targetCell.Interior.ColorIndex = 3
      Next watchCell
      Else: cell.Interior.ColorIndex = xlNone
      End If
  Next cell
  </code></pre>
  
  <p>End Sub</p>