title: VBA Macro: Formula is Based on a Column that changes locations
body: <p>i'm trying to tweak my macro so that it creates a column next to a specific column that always changes positions. In the macro i have below, it is just an absolute reference of 6 columns to the left. However, this wont always be the case. Should I set this up by finding the column name in the top row? </p>
  
  <p>Basically the macro creates a new column and puts in an IF statement if it is a duplicate, and then sets up conditional formatting to highlight all the values of "1". Sorry if i am not explaining this clearly!</p>
  
  <pre><code>Sub test()
      Columns("L:L").Select
      Selection.Insert Shift:=xlToRight
      Range("L2").Select
      ActiveCell.FormulaR1C1 = "=IF(RC[-6]=R[-1]C[-6],R[-1]C+1,1)"
      Range("L2").Select
      Selection.Copy
      Range("K2").Select
      Selection.End(xlDown).Select
      ActiveCell.Offset(0, 1).Select
      Range(Selection, Selection.End(xlUp)).Select
      ActiveSheet.Paste
      Application.CutCopyMode = False
      Calculate
      Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
          Formula1:="=1"
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Font
          .Color = -16383844
          .TintAndShade = 0
      End With
      With Selection.FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .Color = 13551615
          .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = False
  End Sub
  </code></pre>