Results 1 to 4 of 4

Thread: How to find column via header name, then compare that column with another worksheet and highlight the difference.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to find column via header name, then compare that column with another worksheet and highlight the difference.

    I am a newbie here, also a newbie in VBA.

    I have two worksheets that I wanted to compare.

    Let's say,

    Sheet1
    Column DWG. NO
    Column SYM
    Sheet2
    Column DWG. NO
    Column SYM
    But the column position of DWG and SYM in sheet2 is not always the same so first I need to locate the positions of the column before comparing. Then highlight the comparison.

    Take note also that the sheets have thousands of row data and multiple columns. But only two columns are needed to be compared.

    I am using below #Code

    Sub LookForMatches()
    Dim rng1 As Range, rng2 As Range, i As Range, c1 As Range, c2 As Range
    Dim rng3 As Range, rng4 As Range, j As Range, c3 As Range, c4 As Range
    'set ranges
    Set rng1 = Sheets("datax").Range("C5", Sheets("datax").Range("C" & Rows.Count).End(xlUp))
    Set i = Range("DWG. NO").Find("*", Range("DWG. NO")(1), , , xlPrevious)
    Set rng2 = Sheets("datay").Range("i", Sheets("datay").Range("i" & Rows.Count).End(xlUp))
    Set rng3 = Sheets("datax").Range("F5", Sheets("datax").Range("F" & Rows.Count).End(xlUp))
    Set j = Range("SYM").Find("*", Range("SYM")(1), , , xlPrevious)
    Set rng4 = Sheets("datay").Range("j", Sheets("datay").Range("j" & Rows.Count).End(xlUp))
    'reset colour
    rng1.Interior.Color = 16777215
    rng2.Interior.Color = 16777215
    rng3.Interior.Color = 16777215
    rng4.Interior.Color = 16777215
    'loop values in range
    For Each c1 In rng1
    If Not c1.Interior.ColorIndex = 16777215 And c1 <> "" And c1 <> 0 Then
    For Each c2 In rng2
    If c1 = c2 And c2.Address <> c1.Address Then
    c1.Interior.Color = RGB(255, 255, 0)
    c2.Interior.Color = RGB(255, 255, 0)
    End If
    Next c2
    End If
    Next c1
    'loop values in next range
    For Each c3 In rng3
    If Not c3.Interior.ColorIndex = 16777215 And c3 <> "" And c3 <> 0 Then
    For Each c4 In rng4
    If c3 = c4 And c4.Address <> c3.Address Then
    c3.Interior.Color = RGB(255, 255, 0)
    c4.Interior.Color = RGB(255, 255, 0)
    End If
    Next c4
    End If
    Next c3
    End Sub

    This is not working.
    Error 1004 pops-up.

    Thank you so much

  2. #2
    New Member
    Join Date
    Jul 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find column via header name, then compare that column with another worksheet and highlight the difference.

    I got the title wrong..
    It should be *Highlight the SIMILARITIES.
    Thanks

  3. #3
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,154
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: How to find column via header name, then compare that column with another worksheet and highlight the difference.

    welcome

    Looping through thousands of rows of data like that is slow. Generally there are other approaches that truly are orders of magnitude faster.

    Suggest you explain in words what needs to be done.

    Also, can another column be populated with TRUE/FALSE to indicate matches? Instead of the colours? This could be more efficient. Possibly even populated via formula & hence avoiding looping.

    Sample data always help too. And also the Excel version. regards, Fazza
    To receive a better answer, put more work into asking the question.


  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find column via header name, then compare that column with another worksheet and highlight the difference.

    Hi,

    Thanks fo entertaining my question. I really appreciate it.

    This is what i need to be done.
    I have two sheets.
    datax and data y wich contains many columns and rows.

    I need to compare 2 columns of datax to datay ad highlight the similar values.
    However, datay column location of the 2 column data are not fix.

    In sheet1 the column lets say SYM and DWG. NO location are fix in C and F while in sheet2 the SYM. and DWG. NO column are not fix.
    So I wanted to use header name which is SYM. and DWG. NO to locate the columns of sheet.

    With that the problem exist.

    Sorry, I am not so knowledgeabe with macro vba so I do no know what to use to make it simple.
    If I am not mistaken the version is 2016 and higher I do not know exactly but I am using the Microsoft Office 365 ProPlus

    Thank you.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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