VBA Matching and Highlight the text

A

Allan3

New Member
Joined
Apr 24, 2019
Messages
4
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!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
This simple macro might help

Code: 
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
 
Upvote 0
or this ...
Code: 
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
N​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Header[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td=bgcolor:#C8C8C8]
5.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td=bgcolor:#C8C8C8]
3.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td=bgcolor:#C8C8C8]
5.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td=bgcolor:#FFFFFF]
5.7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#C8C8C8]
5.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td=bgcolor:#C8C8C8]
1.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td=bgcolor:#C8C8C8]
3.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td=bgcolor:#FFFFFF]
3.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td=bgcolor:#C8C8C8]
1.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td=bgcolor:#C8C8C8]
1.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td=bgcolor:#FFFFFF]
1.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td=bgcolor:#C8C8C8]
4.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td=bgcolor:#FFFFFF]
-7.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td=bgcolor:#C8C8C8]
-5.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td=bgcolor:#C8C8C8]
-2.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td=bgcolor:#FFFFFF]
-7.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td=bgcolor:#FFFFFF]
-4.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td=bgcolor:#FFFFFF]
-9.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td=bgcolor:#C8C8C8]
-4.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td=bgcolor:#C8C8C8]
-1.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td=bgcolor:#C8C8C8]
-6.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td=bgcolor:#C8C8C8]
-5.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td=bgcolor:#FFFFFF]
-1.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td=bgcolor:#C8C8C8]
-3.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
26​
[/td][td=bgcolor:#FFFFFF]
-9.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
27​
[/td][td=bgcolor:#C8C8C8]
-5.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
28​
[/td][td=bgcolor:#FFFFFF]
-3.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
29​
[/td][td=bgcolor:#C8C8C8]
-6.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
30​
[/td][td=bgcolor:#C8C8C8]
-1.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
31​
[/td][td=bgcolor:#C8C8C8]
-5.0​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
N​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Header[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td=bgcolor:#FFFFFF]
-18.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td=bgcolor:#FFFFFF]
-17.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td=bgcolor:#C8C8C8]
-3.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td=bgcolor:#FFFFFF]
-18.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#FFFFFF]
-19.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td=bgcolor:#C8C8C8]
-3.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td=bgcolor:#C8C8C8]
-5.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td=bgcolor:#C8C8C8]
-2.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td=bgcolor:#FFFFFF]
-20.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td=bgcolor:#FFFFFF]
-14.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td=bgcolor:#C8C8C8]
-5.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td=bgcolor:#FFFFFF]
-20.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td=bgcolor:#C8C8C8]
-5.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td=bgcolor:#FFFFFF]
-17.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td=bgcolor:#C8C8C8]
-2.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td=bgcolor:#C8C8C8]
-5.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td=bgcolor:#C8C8C8]
4.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td=bgcolor:#C8C8C8]
5.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td=bgcolor:#FFFFFF]
19.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td=bgcolor:#C8C8C8]
2.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td=bgcolor:#FFFFFF]
18.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td=bgcolor:#C8C8C8]
1.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td=bgcolor:#C8C8C8]
2.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td=bgcolor:#C8C8C8]
1.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
26​
[/td][td=bgcolor:#C8C8C8]
4.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
27​
[/td][td=bgcolor:#FFFFFF]
19.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
28​
[/td][td=bgcolor:#C8C8C8]
6.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
29​
[/td][td=bgcolor:#FFFFFF]
17.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
30​
[/td][td=bgcolor:#C8C8C8]
2.0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
31​
[/td][td=bgcolor:#C8C8C8]
4.0​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]
 
Upvote 0
To ignore blank cells and zeros

Amend one line in code in post#3

Code: 
If Not c1.Interior.ColorIndex = 16777215 Then

If Not c1.Interior.ColorIndex = 16777215 [COLOR=#ff0000]And c1 <> "" And c1 <> 0[/COLOR] Then
 
Upvote 0
If you want to clear highlight before the search

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

Code: 
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
 
Upvote 0
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:
[TABLE="width: 50"]
<tbody>[TR]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]-3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]-6[/TD]
[/TR]
[TR]
[TD="align: center"]-2[/TD]
[/TR]
[TR]
[TD="align: center"]-5[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]-7[/TD]
[/TR]
[TR]
[TD="align: center"]-9[/TD]
[/TR]
</tbody>[/TABLE]

The same number (ignoring +/-) will be highlighted and the unmatched (1,8) will remain unhighlight.
 
Upvote 0
What if I wanted to lookformatches within the same column, how can I can the macro to match within the same column
Click to expand...
Code: 
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
 
Last edited:
Upvote 0
Yongle said:
Code: 
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
Click to expand...



Thank you very much. Much appreciate to both of your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Highlight text in column 1 based on a range of words* in column 2
Replies
6
Views
205
Fluff
Fluff
D
  • Solved
Conditional Formatting Finding Multiple Matched Values
Replies
6
Views
608
Joe4
Joe4
M
  • Solved
VBA to Insert Mismatched Row in separate worksheet
2 3
Replies
21
Views
753
Peter_SSs
Peter_SSs
O
  • Question Question
IF cell contains text found in another column
Replies
2
Views
354
kvsrinivasamurthy
K
P
  • Question Question
If 1st 3 Digits of Column A match First 3 Digits Of Column B highlight both cell with a color
2
Replies
18
Views
574
Pankti
P

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top