title: VBA is changing constant values of its own accord
body: <p>I am working on a section of a fairly large VBA macro that is designed to apply conditional formatting when another cell is equal to 1, so that the cell can be highlighted as 'selected.'<br>
  When generating the conditional formatting formula that does this, excel seems to be changing constant values of its own accord - all the values it reads from should be at row 99 and this is in the code, but excel instead changes the formula to <code>=IF(B101=1,1,0)</code> for the first one, then <code>=IF(B105=1,1,0)</code> for the next one and so on.<br>
  These are merged cells, so I would assume that excel is trying to autocomplete my code by offsetting the formula by how many rows down I am. Obviously this is not desired, does anyone know how I can stop excel from doing this? The relevant section of code is below:</p>
  
  <pre><code>    For Each x In Range(crange)
              condrow = 99
              Range(Cells(coffset + 3, y + 1), Cells(coffset + 2 + currentdiv, y + 1)).FormatConditions.Delete
              Cells(coffset + 3, y + 1).Value = x
              selectform = "=IF(" &amp; ConvertToLetter(Cells(coffset + 3, y + 1).Column) &amp; CStr(condrow) &amp; "=1,1,0)"
              Range(Cells(coffset + 3, y + 1), Cells(coffset + 2 + currentdiv, y + 1)).Merge
          Range(Cells(coffset + 3, y + 1), Cells(coffset + 2 + currentdiv, y + 1)).FormatConditions.Delete
          Range(Cells(coffset + 3, y + 1), Cells(coffset + 2 + currentdiv, y + 1)).FormatConditions.Add Type:=xlExpression, Formula1:= _
              "clashform"
  </code></pre>