title: Dynamic Conditional Formatting With VBA Macro
body: <p>I'm fairly new to VBA. I want to conditionally format the cells of the column of the ActiveCell so that they are highlighted blue if they are not present in the column which is 4 columns to the right of the ActiveCell. I recorded a macro which performed this for one particular set of columns but I am having trouble changing it so that it works for arbitrary sets of columns based on the position of the ActiveCell. The recorded code is given below</p>
  
  <pre><code>    Range("Y8").Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=COUNTIF(AC:AC, Y8)=0"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Interior
      .PatternColorIndex = xlAutomatic
      .Color = 15773696
      .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  ActiveWindow.SmallScroll Down:=0
  </code></pre>
  
  <p>Could anyone please tell me how to alter this so that Y8 is the ActiveCell and AC:AC is the column 4 columns to the right of the ActiveCell. Thank you.</p>