title: copy/pastspecial cell format in VBA User-Defined Function
body: <p>I have a formula in VBA that:
  - Executes VLOOKUP
  - Find the address of the cell that has the value found by vlookup
  - PROBLEM: Copy/pastespecial original cell's format
  - Return Value of vlookup</p>
  
  <pre><code>Function VLOOKUPnew(ValueToLook As Range, Interval As Range, ColIndex As Integer) As Variant
  
  Dim fMatch, fVlookup
  
  Dim ColMatchIndex
  Dim CellOrigin, CellDestination
  
  ' ********************************************************************************
  ' LOOKUP:: Application.VLookup(ValueToLook, Interval, colIndex, False)
  ' MATCH:: Application.Match(ValueToLook, Range(Interval.Address()).Columns(1), 0)
  ' ********************************************************************************
  
  ' Indice da 1ª coluna do Intervalo
  ' **********************************************
  ColMatchIndex = Interval.Columns(Interval.Columns.Count - 1).Column
  ' **********************************************
  
  fMatch = Application.Match(ValueToLook, Range(Interval.Address()).Columns(1), 0)
  
  ' Obtem o endereço da célula que contem o valor do VLOOKUP
  ' **********************************************
  CellOrigin = Interval.Cells(fMatch, Interval.Columns.Count).Address()
  ' **********************************************
  
  ' Copia a Formatação da Célula encontrada pelo Vlookup
  ' ******************************************************************
  Range(CellOrigin).Copy
  ActiveCell.PasteSpecial (xlPasteFormats)
  Application.CutCopyMode = False
  ' ******************************************************************
  
  VLOOKUPnew = Application.VLookup(ValueToLook, Interval, ColIndex, False)
  End Function
  </code></pre>
  
  <p>Some tips to keep in mind:
  - User must write function - VLOOKUPnew - like he writes normal vlookup formula at excel
  - After press enter, the function must return vlookupnew results his cell's format</p>
  
  <p>Don't copy/paste cell's format. Any tip?</p>
  
  <p>Printscreen of my goal
  <a href="http://snag.gy/rZJ8n.jpg" rel="nofollow">Printscreen of excel</a></p>