title: Excel macro vlookup with formatting
body: <p>I found this code through Google and I'm trying to get it to work for me. 
  <a href="https://stackoverflow.com/questions/22151426/vlookup-to-copy-color-of-a-cell-excel-vba">Vlookup to copy color of a cell - Excel VBA</a></p>
  
  <p>I have an Excel workbook in which I want to easily <code>VLOOKUP</code> values and formatting from an Excel sheet I did the week before. </p>
  
  <p>I can download a list from our database with pricing. 
  Then manually a from/to price is added in the cells. This happens every week. I have to copy paste it manually every week because the <code>VLOOKUP</code> doesn't add the formatting. </p>
  
  <p>Here is source (oud) looks:
  <a href="https://i.stack.imgur.com/5xO2j.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5xO2j.png" alt="enter image description here"></a></p>
  
  <p>My <code>VLOOKUP</code> returns the values correctly, I just want the formatting as well. </p>
  
  <pre><code>=VLOOKUP(A2;oud!A:D;4;FALSE)
  </code></pre>
  
  <p>I cant get the earlier mentioned macro on the link to work. I'm fairly new to VBA so treat me as a newby ;) </p>
  
  <p>Anybody can help? It would save me a lot of time every week :)</p>
  
  <pre><code>Option Explicit
  ' By StackOverflow user LondonRob
  ' See http://stackoverflow.com/questions/22151426/vlookup-to-copy-color-of-a-cell-excel-vba
  
  Public Sub formatSelectionByLookup()
    ' Select the range you'd like to format then
    ' run this macro
    copyLookupFormatting Selection
  
  End Sub
  
  Private Sub copyLookupFormatting(destRange As Range)
    ' Take each cell in destRange and copy the formatting
    ' from the destination cell (either itself or
    ' the vlookup target if the cell is a vlookup)
    Dim destCell As Range
    Dim srcCell As Range
  
    For Each destCell In destRange
      Set srcCell = getDestCell(destCell)
      copyFormatting destCell, srcCell
    Next destCell
  
  End Sub
  
  Private Sub copyFormatting(destCell As Range, srcCell As Range)
    ' Copy the formatting of srcCell into destCell
    ' This can be extended to include, e.g. borders
    destCell.Font.Color = srcCell.Font.Color
    destCell.Font.Bold = srcCell.Font.Bold
    destCell.Font.Size = srcCell.Font.Size
  
    destCell.Interior.Color = srcCell.Interior.Color
  
  End Sub
  
  Private Function getDestCell(fromCell As Range) As Range
    ' If fromCell is a vlookup, return the cell
    ' pointed at by the vlookup. Otherwise return the
    ' cell itself.
    Dim srcColNum As Integer
    Dim srcRowNum As Integer
    Dim srcRange As Range
    Dim srcCol As Range
  
    srcColNum = extractLookupColNum(fromCell)
    Set srcRange = extractDestRange(fromCell)
    Set srcCol = getNthColumn(srcRange, srcColNum)
    srcRowNum = Application.Match(fromCell.Value, srcCol, 0)
    Set getDestCell = srcRange.Cells(srcRowNum, srcColNum)
  
  End Function
  
  Private Function extractDestRange(fromCell As Range) As Range
    ' Get the destination range of a vlookup in the formulat
    ' of fromCell. Returns fromCell itself if no vlookup is
    ' detected.
    Dim fromFormula As String
    Dim startPos As Integer
    Dim endPos As Integer
    Dim destAddr As String
  
    fromFormula = fromCell.Formula
  
    If Left(fromFormula, 9) = "=VLOOKUP(" Then
      startPos = InStr(fromFormula, ",") + 1
      endPos = InStr(startPos, fromFormula, ",")
      destAddr = Trim(Mid(fromFormula, startPos, endPos - startPos))
    Else
      destAddr = fromCell.Address
    End If
    Set extractDestRange = fromCell.Parent.Range(destAddr)
  
  End Function
  
  Private Function extractLookupColNum(fromCell As Range) As Integer
    ' If fromCell contains a vlookup, return the number of the
    ' column requested by the vlookup. Otherwise return 1
    Dim fromFormula As String
    Dim startPos As Integer
    Dim endPos As Integer
    Dim colNumber As String
  
    fromFormula = fromCell.Formula
  
    If Left(fromFormula, 9) = "=VLOOKUP(" Then
      startPos = InStr(InStr(fromFormula, ",") + 1, fromFormula, ",") + 1
      endPos = InStr(startPos, fromFormula, ",")
      If endPos &lt; startPos Then
        endPos = InStr(startPos, fromFormula, ")")
      End If
      colNumber = Trim(Mid(fromFormula, startPos, endPos - startPos))
    Else
      colNumber = 1
    End If
  
    extractLookupColNum = colNumber
  
  End Function
  
  Private Function getNthColumn(fromRange As Range, n As Integer) As Range
    ' Get the Nth column from fromRange
    Dim startCell As Range
    Dim endCell As Range
  
    Set startCell = fromRange(1).Offset(0, n - 1)
    Set endCell = startCell.End(xlDown)
  
    Set getNthColumn = Range(startCell, endCell)
  
  End Function
  </code></pre>
  
  <p>I copied the code pasted into a macro sheet. Added a button in my workbook that linked to the macro. Selected the range of the net price column where I have the vlookup. Then I press the button and get this error that leads to: </p>
  
  <pre><code>Set extractDestRange = fromCell.Parent.Range(destAddr) 
  </code></pre>
  
  <blockquote>
    <p>run-time error '1004': application-defined or object defined error</p>
  </blockquote>