VBA Matching and Highlight the text

[Allan3]

Hi guys,

I am new here and starting to learn about macro.

Currently I would like to create a macro that enable me match the value that are the same (only numerical and ignore the +/- sign) and highlight the value that are same in yellow.

For example, I have 2 worksheet. One is the actual and another is the raw data.
Actual as Worksheet 1 and Raw data as 2
I would like to match the Column N of Worksheet 1 with the Worksheet 2, column N and check whether is it the same.
If they are the same (ignore the +/- sign), highlight it and if they are not, then left it blank.

I was stuck when I tried to create a MATCH formula for it. Is there anyone who is able to help me?
Much appreciate!

 

[nemmi69]

This simple macro might help

Option Explicit


Sub CompareColumns()
Dim LastRowNo1 As Long, LastRowNo2 As Long
Dim Sheet1Loop As Long, Sheet2Loop As Long


LastRowNo1 = Worksheets("Sheet1").Range("N65536").End(xlUp).Row
LastRowNo2 = Worksheets("Sheet2").Range("N65536").End(xlUp).Row


For Sheet1Loop = 1 To LastRowNo1
    For Sheet2Loop = 1 To LastRowNo2
        If Worksheets("Sheet1").Range("N" & Sheet1Loop).Value = Worksheets("Sheet2").Range("N" & Sheet2Loop).Value Then
            With Worksheets("Sheet1").Range("N" & Sheet1Loop).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent4
                .TintAndShade = 0.399945066682943
                .PatternTintAndShade = 0
            End With
            With Worksheets("Sheet2").Range("N" & Sheet2Loop).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent4
                .TintAndShade = 0.399945066682943
                .PatternTintAndShade = 0
            End With


        End If
    Next Sheet2Loop
Next Sheet1Loop
End Sub

 

[Yongle]

or this ...

Sub LookForMatches()
    Dim rng1 As Range, rng2 As Range, c1 As Range, c2 As Range
'set ranges
    Set rng1 = Sheets("Sheet1").Range("N2", Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp))
    Set rng2 = Sheets("Sheet2").Range("N2", Sheets("Sheet2").Range("N" & Rows.Count).End(xlUp))
'reset colour
    rng1.Interior.Color = 16777215
    rng2.Interior.Color = 16777215
'loop values in range
    For Each c1 In rng1
        If Not c1.Interior.ColorIndex = 16777215 Then
            For Each c2 In rng2
                If Abs(c1) = Abs(c2) Then
                    c1.Interior.Color = RGB(200, 200, 200)
                    c2.Interior.Color = RGB(200, 200, 200)
                End If
            Next c2
        End If
    Next c1
End Sub

Excel 2016 (Windows) 32 bit

	N​
1​	Header
2​	5.0​
3​	3.0​
4​	5.0​
5​	5.7​
6​	5.0​
7​	1.0​
8​	3.0​
9​	3.1​
10​	1.0​
11​	1.0​
12​	1.1​
13​	4.0​
14​	-7.0​
15​	-5.0​
16​	-2.0​
17​	-7.0​
18​	-4.2​
19​	-9.0​
20​	-4.0​
21​	-1.0​
22​	-6.0​
23​	-5.0​
24​	-1.1​
25​	-3.0​
26​	-9.0​
27​	-5.0​
28​	-3.2​
29​	-6.0​
30​	-1.0​
31​	-5.0​

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	N​
1​	Header
2​	-18.0​
3​	-17.0​
4​	-3.0​
5​	-18.0​
6​	-19.0​
7​	-3.0​
8​	-5.0​
9​	-2.0​
10​	-20.0​
11​	-14.0​
12​	-5.0​
13​	-20.0​
14​	-5.0​
15​	-17.0​
16​	-2.0​
17​	-5.0​
18​	4.0​
19​	5.0​
20​	19.0​
21​	2.0​
22​	18.0​
23​	1.0​
24​	2.0​
25​	1.0​
26​	4.0​
27​	19.0​
28​	6.0​
29​	17.0​
30​	2.0​
31​	4.0​

Sheet: Sheet2

 

[Allan3]

Wow, thank you very much nemmi69

 

[Yongle]

To ignore blank cells and zeros

Amend one line in code in post#3

If Not c1.Interior.ColorIndex = 16777215 Then

If Not c1.Interior.ColorIndex = 16777215 [COLOR=#ff0000]And c1 <> "" And c1 <> 0[/COLOR] Then

 

[nemmi69]

If you want to clear highlight before the search

Add this code just after "LastRowNo2 = Worksheets("Sheet2").Range("N65536").End(xlUp).Row"

With Worksheets("Sheet1").Range("N:N").Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
With Worksheets("Sheet2").Range("N:N").Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

 

[Allan3]

Hi Yongle,

Thank you for your VBA. I wanted to check with you regarding the post#3.

What if I wanted to lookformatches within the same column, how can I can the macro to match within the same column (was trying it out, but it highlighted every cell of column N.

For example the data as below:

N
-3
5
2
6
3
-6
-2
-5
1
7
8
9
-7
-9

<tbody>
</tbody>

The same number (ignoring +/-) will be highlighted and the unmatched (1,8) will remain unhighlight.

 

[Yongle]

What if I wanted to lookformatches within the same column, how can I can the macro to match within the same column

Sub AbsoluteMatchesInSameRange()
    Dim rng1 As Range, c1 As Range, c2 As Range
'set ranges
    Set rng1 = Sheets("Sheet1").Range("N2", Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp))
'reset colour
    rng1.Interior.Color = 16777215
'loop values in range
    For Each [COLOR=#ff0000]c1[/COLOR] In [COLOR=#ff0000]rng1[/COLOR]
        If Not c1.Interior.ColorIndex = 16777215 And c1 <> "" And c1 <> 0 Then
            For Each [COLOR=#ff0000]c2[/COLOR] In [COLOR=#ff0000]rng1[/COLOR]
                If Abs(c1) = Abs(c2) And [COLOR=#ff0000]c2.Address <> c1.Address [/COLOR]Then
                    c1.Interior.Color = RGB(200, 200, 200)
                    c2.Interior.Color = RGB(200, 200, 200)
                End If
            Next c2
        End If
    Next c1
End Sub

Above ignores zero and blanks

 

[Allan3]

Sub AbsoluteMatchesInSameRange()
    Dim rng1 As Range, c1 As Range, c2 As Range
'set ranges
    Set rng1 = Sheets("Sheet1").Range("N2", Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp))
'reset colour
    rng1.Interior.Color = 16777215
'loop values in range
    For Each [COLOR=#ff0000]c1[/COLOR] In [COLOR=#ff0000]rng1[/COLOR]
        If Not c1.Interior.ColorIndex = 16777215 And c1 <> "" And c1 <> 0 Then
            For Each [COLOR=#ff0000]c2[/COLOR] In [COLOR=#ff0000]rng1[/COLOR]
                If Abs(c1) = Abs(c2) And [COLOR=#ff0000]c2.Address <> c1.Address [/COLOR]Then
                    c1.Interior.Color = RGB(200, 200, 200)
                    c2.Interior.Color = RGB(200, 200, 200)
                End If
            Next c2
        End If
    Next c1
End Sub

Above ignores zero and blanks

Thank you very much. Much appreciate to both of your help.