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

[Jpngineer]

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

 

[Jpngineer]

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

 

[Fazza]

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

 

[Jpngineer]

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.