Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Mar 2016
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    VBA to match two ranges then copy fill color from source range to destination range

    I have data in a column "B" on sheet 2 and it is conditional formatted. Then I have similar data scattered in a range B1M17 on sheet 1.
    I am looking for a vba code that would match sheet 1 data to sheet 2 data and then copy fill color of the matching cell from column "B" of sheet 2 to matching cell of sheet 1 range B1:B17.
    Thank you for you help in advance and please let me know if you will need any other information.
    Attached Files Attached Files
    Last edited by xxsinghxx; 2016-03-11 at 17:18.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Welcome to the forum.

    You have conditional formatted cells in col A on sheet 2 based on the value of the its adjacent cell in column B. Would it be correct to interpret your request that it is the values in column A that you want matched with the scattered data in Sheet 1 then copy over the cell fill color?

    You have 42 duplicates on sheet 2 in column A. How do you want to handle them when matched with sheet 1?

    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    xxsinghxx (2016-03-12)

  4. #3
    New Lounger
    Join Date
    Mar 2016
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Welcome to the forum.

    You have conditional formatted cells in col A on sheet 2 based on the value of the its adjacent cell in column B. Would it be correct to interpret your request that it is the values in column A that you want matched with the scattered data in Sheet 1 then copy over the cell fill color?

    You have 42 duplicates on sheet 2 in column A. How do you want to handle them when matched with sheet 1?

    Maud
    Maud, thank you for the quick reply.

    Sorry for the confusion. Yes, you are right for the col A on sheet 2 as value to be matched with the sheet1 scattered data and then copy over the cell fill color on the sheet 1 data.

    I am surprised to see any dups. there should have not been any. Is it feasible to eliminate the dups first then execute the code? thank you.

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    This task would serve well as a stress test to benchmark your CPU. I tried this several ways and this by far was the fasted method.

    Code:
    Public Sub FindMatch()
    Application.ScreenUpdating = False
    For I = 2 To 12041
        Set keyword = Sheets("Sheet1").UsedRange.Find(What:=Sheets("Sheet2").Cells(I, 1))
        If Not keyword Is Nothing Then
            Select Case Sheets("Sheet2").Cells(I + 1, 2)
                Case "A":: keyword.Interior.Color = vbGreen
                Case "B":: keyword.Interior.Color = vbYellow
                Case "C":: keyword.Interior.Color = vbRed
                Case "D":: keyword.Interior.Color = 8421504
            End Select
        End If
    Next I
    Application.ScreenUpdating = False
    End Sub
    SampleData.png

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2016-03-12 at 01:53.

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    ..with minor amendments to your code I managed to reduce time taken on my laptop from 14 seconds to 1 second.

    see attached

    zeddy
    always willing to have a go at a speed challenge
    Attached Files Attached Files

  7. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    Maudibe (2016-03-12),xxsinghxx (2016-03-12)

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Wow!!! Match function is so much quicker than the Find function. I'll tuck that one away

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    ..it's probably not the speed difference between MATCH and FIND.
    I cheated.
    Instead of looping through 12,041 rows on sheet2, I looped through the 1,251 cells on sheet1.
    On my second laptop, your routine took only 6 seconds, so my time saved wasn't so much.

    zeddy

  10. The Following User Says Thank You to zeddy For This Useful Post:

    xxsinghxx (2016-03-12)

  11. #8
    New Lounger
    Join Date
    Mar 2016
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Awesome!! Thank you very much Maud

  12. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    xxsinghxx,

    Your welcome but honestly, I like zeddy's modifications. I would use his code provided your data in sheet 2 was in only one column.

    Maud

  13. #10
    New Lounger
    Join Date
    Mar 2016
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Nice work Sir! Thank you. Now how about if I have destination data in multiple spreadsheets and the source data is same for all SHEET2.

  14. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    See attached sample with more sheets and same source data.

    The code was adjusted to use
    For Each cell In ActiveSheet.UsedRange

    ditto for the [Reset] button

    zeddy
    Attached Files Attached Files

  15. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    ..and while I remember, if you want to remove duplicates in column [A] on [Sheet2], you can use Excel's Ribbon command (in the [Data] section) to Remove Duplicates
    (assuming you are using Excel2007 onwards)

    zeddy

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •