title: Use VBA to copy conditionally formatted cells
body: <p>I am writing a script that copies data from one workbook to another. The latter is being used as a sort of database (not my idea). As a test I am copying ~300 rows of data, of which 3 columns conditional formatting and the rest are plain text. Copying the text is easy and near instantaneous but the formatting is more difficult.  Currently I am using the below code to copy the formatted cells:</p>
  
  <pre><code>thisSheet.Range("G" &amp; CStr(rRow), "I" &amp; CStr(rRow)).Copy
  masterSheet.Range("G" &amp; CStr(mRow), "I" &amp; CStr(mRow)).PasteSpecial (xlPasteAll)
  </code></pre>
  
  <p>With ~300 rows this takes about 40 seconds, which is too slow.  I cannot copy a range consisting of multiple rows as they are not pasted sequentially.</p>
  
  <p>I experimented with the following code to try and copy formats.</p>
  
  <pre><code>masterSheet.Range("G" &amp; CStr(mRow), "I" &amp; CStr(mRow)).value = thisSheet.Range("G" &amp; CStr(rRow), "I" &amp; CStr(rRow)).value
  masterSheet.Range("G" &amp; CStr(mRow), "I" &amp; CStr(mRow)).Font.Color = thisSheet.Range("G" &amp; CStr(rRow), "I" &amp; CStr(rRow)).Font.Color
  masterSheet.Range("G" &amp; CStr(mRow), "I" &amp; CStr(mRow)).Interior.ColorIndex = thisSheet.Range("G" &amp; CStr(rRow), "I" &amp; CStr(rRow)).Interior.ColorIndex
  'cell color and font color are the only things i am interested in
  </code></pre>
  
  <p>This code executes in about 3 seconds, but none of the formatting applied by the conditional formatting is copied.</p>
  
  <p>Is there a more efficient way of copying cell and font colors that were applied by conditional formatting?</p>