title: Excel VBA - Referencing Range using Cells
body: <p>I am trying to dynamically add <code>validation</code> (using DataValidation:List) to ranges in a worksheet.  I recorded a macro that produced the following code:</p>
  
  <pre><code>With Worksheets("Clusters").Range("C2:C100").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:="=Managers"
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
  End With
  </code></pre>
  
  <p><br>
  This was working fine for the static range C2:C100, but the column may not be always be C.  I have a variable cMANFCM that contains the column number.  I tried to edit the code to use this:</p>
  
  <pre><code>With Worksheets("Clusters").Range(Cells(2,cMANFCM), Cells(100, cMANFCM)).Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:="=Managers"
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
  End With
  </code></pre>
  
  <p><br>
  Why doesn't this work and how do I fix it?</p>