title: Why is my conditional format offset when added by VBA?
body: <p>I was trying to add conditional formats like this:</p>
  
  <p>If expression <code>=($G5&lt;&gt;"")</code> then make set interior green, use this for $A$5:$H$25.</p>
  
  <p>Tried this, worked fine, as expected, then tried to adapt this as VBA-Code with following code, which is working, but not as expected:</p>
  
  <pre><code>With ActiveSheet.UsedRange.Offset(1)
    .FormatConditions.Delete
    'set used row range to green interior color, if "Erledigt Datum" is not empty
    With .FormatConditions.Add(Type:=xlExpression, _
                               Formula1:="=($" &amp; cstrDefaultProgressColumn &amp; _
                                                        .row &amp; "&lt;&gt;"""")")
          .Interior.ColorIndex = 4
        End With
  End With
  </code></pre>
  
  <p><strong>The Problem is</strong>, <code>.row</code> is providing the right row while in debug, however my added conditional-formula seems to be one or more rows off - depending on my solution for setting the row. So I am ending up with a conditional formatting, which has an offset to the row, which should have been formatted.</p>
  
  <p>In the dialog it is then <code>=($G6&lt;&gt;"")</code> or G3 or G100310 or something like this. But not my desired G5.</p>
  
  <p>Setting the row <em>has</em> to be dynamicall, because this is used to setup conditional formats on different worksheets, which can have their data starting at different rows.</p>
  
  <p>I was suspecting my <code>With</code> arrangement, but it did not fix this problem.</p>
  
  <p><strong>edit:</strong> To be more specific, this is <strong>NOT</strong> a UsedRange problem, having the same trouble with this:</p>
  
  <pre><code>Dim rngData As Range
  Set rngData = ActiveSheet.Range("A:H") 'ActiveSheet.UsedRange.Offset(1)
  
  rngData.FormatConditions.Delete
  
  With rngData.FormatConditions.Add(Type:=xlExpression, _
                                    Formula1:="=($" &amp; cstrDefaultProgressColumn &amp; _
                                                    1 &amp; "&lt;&gt;"""")")
      .Interior.ColorIndex = 4
  End With
  </code></pre>
  
  <p>My Data looks like this:</p>
  
  <pre><code>1 -&gt; empty cells
  2 -&gt; empty cells
  3 -&gt; empty cells
  4 -&gt; TitleCols -&gt; A;B;C;...;H
  5 -&gt; Data to TitleCols
  . .
  . .
  . .
  25
  </code></pre>
  
  <p>When I execute this edited code on Excel 2007 and lookup the formula in the conditional dialog it is <code>=($G1048571&lt;&gt;"")</code> - it should be <code>=($G1&lt;&gt;"")</code>, then everything works fine.</p>
  
  <p>Whats even more strange - this is an edited version of a fine working code, which used to add conditional formats for each row. But then I realized, that it's possible to write an expression, which formats a whole row or parts of it - thought this would be adapted in a minute, and now this ^^</p>
  
  <p><strong>edit:</strong> <em>Additional task informations</em></p>
  
  <p>I use conditional formatting here, because this functions shall setup a table to react on user input. So, if properly setup and a user edits some cell in my conditionalized column of this tabel, the corresponding row will turn green for the used range of rows.</p>
  
  <p>Now, because there might be rows before the main header-row and there might be a various number of data-columns, and also the targeted column may change, I do of course use some specific informations.</p>
  
  <p>To keep them minimal, I do use NamedRanges to determine the correct offset and to determine the correct <code>DefaultProgessColumn</code>. </p>
  
  <p><code>GetTitleRow</code> is used to determine the header-row by NamedRange or header-contents.</p>
  
  <pre><code>With ActiveSheet.UsedRange.Offset(GetTitleRow(ActiveSheet.UsedRange) - _
                                  ActiveSheet.UsedRange.Rows(1).row + 1)
  </code></pre>
  
  <p>Corrected my Formula1, because I found the construct before not well formed.</p>
  
  <pre><code>Formula1:="=(" &amp; Cells(.row, _
             Range(strMatchCol1).Column).Address(RowAbsolute:=False) &amp; _
             "&lt;&gt;"""")"
  </code></pre>
  
  <p><code>strMatchCol1</code> - is the name of a range.</p>