title: fast way to copy formatting in excel
body: <p>I have two bits of code. First a standard copy paste from cell A to cell B</p>
  
  <pre><code>Sheets(sheet_).Cells(x, 1).Copy Destination:=Sheets("Output").Cells(startrow, 2)
  </code></pre>
  
  <p>I can do almost the same using </p>
  
  <pre><code>Sheets("Output").Cells(startrow, 2) = Sheets(sheet_).Cells(x, 1)
  </code></pre>
  
  <p>Now this second method is much faster, avoiding copying to clipboard and pasting again. However it does not copy across the formatting as the first method does. The Second version is almost instant to copy 500 lines, while the first method adds about 5 seconds to the time. And the final version could be upwards of 5000 cells.</p>
  
  <p>So my question can the second line be altered to included the cell formatting (mainly font colour) while still staying fast. </p>
  
  <p>Ideally I would like to be able to copy the cell values to a array/list along with the font formatting so I can do further sorting and operations on them before I "paste" them back on to the worksheet.. </p>
  
  <p>So my ideal solution would be some thing like </p>
  
  <pre><code>for x = 0 to 5000
  array(x) = Sheets(sheet_).Cells(x, 1) 'including formatting
  next
  
  for x = 0 to 5000
  Sheets("Output").Cells(x, 1)
  next
  </code></pre>
  
  <p>is it possible to use RTF strings in VBA or is that only possible in vb.net, etc. </p>
  
  <p><em><strong>Answer</em>*</strong></p>
  
  <p>Just to see how my origianl method and new method compar, here are the results or before and after </p>
  
  <p>New code = 65msec</p>
  
  <pre><code>Sheets("Output").Cells(startrow, 2) = Sheets(sheet_).Cells(x, 1)
  Sheets("Output").Range("B" &amp; startrow).Font.ColorIndex = Sheets(sheet_).Range("A" &amp; x).Font.ColorIndex 'copy font colour as well
  </code></pre>
  
  <p>Old code = 1296msec</p>
  
  <pre><code>'Sheets("Output").Cells(startrow, 2).Value = Sheets(sheet_).Cells(x, 1)
  'Sheets(sheet_).Cells(x, 1).Copy
  'Sheets("Output").Cells(startrow, 2).PasteSpecial (xlPasteFormats)
  'Application.CutCopyMode = False
  </code></pre>