CODEWITHSUNDEEP
excelvbacolorsvlookup
S Strickland
S Strickland

Reputation: 19

Excel vba using vlookup to copy color of a cell

I am in need of using VLOOKUP to copy the color of a cell (that is not CF). I tried to follow the vba code that is posted in the thread by LondonRob at Vlookup to copy color of a cell - Excel VBA but am having trouble since not proficient in vba. Vlookup is needed because Names can be in different order.

I have a sample worksheet where the cell colors need to be copied from the 1st tab to the 2nd tab based on a vlookup of name and column numbers. I set up srcCell and destCell named ranges and copy into a module the posted vba.

1st Tab

Name Amt1

Kathy $500 (cell color red)

Mark $350 (cell color green)

2nd Tab

Name Amt1 Amt2

Mark $350 $200 (need $350 in green cell color)

Kathy $500 $400 (need $500 in red cell color)

Can anyone help?

Upvotes: 0

Views: 3583

Answers (1)

Ajeet Shah
Ajeet Shah

Reputation: 19843

If you are unable to understand the solution at: Vlookup to copy color of a cell - Excel VBA, you could try this one instead:

It simply uses Match to find and copies the cells with values and format by default.

FirstTab:

FirstTab

SecondTab:

SecondTab

SecondTab after running the macro:

SecondTab after running the macro

Sub copy_paste_with_format()

    Dim i As Long
    Dim var As Variant

    Dim FirstTab As Worksheet
    Dim SecondTab As Worksheet

    Set FirstTab = Application.Worksheets("FirstTab")
    Set SecondTab = Application.Worksheets("SecondTab")

    For i = 2 To 3
        var = Application.Match(SecondTab.Range("A" & i), FirstTab.Range("A:A"), 0)
        If Not IsError(var) Then
            FirstTab.Range("B" & var).Copy SecondTab.Range("B" & i)
        End If
    Next i

End Sub

Upvotes: 1

Related Questions