Need Assistance- Looking to include a pop up window on my code

[Giovanni03]

Hello everyone,

I need some help with my code, I currently have it set so if a number doesn't match on two different sheets then those numbers are highlighted red on sheet 2. I'm trying to improve this and see if its possible to include a pop up window showing the amount that turned red on sheet 2. For example if 3 out of 100 numbers turned red then it can pop up stating "Found 3" or something like that. if none then it can also pop up stating Found 0.

Here's the code i have.

Option Explicit
Sub highlight()
Dim lr&, r1 As Range, r2 As Range, cell As Range
With Sheets("Sheet 1")
    lr = .Cells(Rows.Count, "M").End(xlUp).Row
    Set r2 = .Range("M1:M" & lr)
End With
With Sheets("Sheet 2")
    lr = .Cells(Rows.Count, "D").End(xlUp).Row
    Set r1 = .Range("D1:D" & lr)
    For Each cell In r1
        If r2.Find(cell.Value) Is Nothing Then cell.Font.Bold = True
        If r2.Find(cell.Value) Is Nothing Then cell.Font.Color = vbRed
    Next
End With
End Sub

 

[Joe4]

Maybe try this:

Sub highlight()

Dim lr As Long, r1 As Range, r2 As Range, cell As Range, ct As Long

With Sheets("Sheet 1")
    lr = .Cells(Rows.Count, "M").End(xlUp).Row
    Set r2 = .Range("M1:M" & lr)
End With
With Sheets("Sheet 2")
    lr = .Cells(Rows.Count, "D").End(xlUp).Row
    Set r1 = .Range("D1:D" & lr)
    For Each cell In r1
        If r2.Find(cell.Value) Is Nothing Then
            cell.Font.Bold = True
            cell.Font.Color = vbRed
            ct = ct + 1
        End If
    Next
End With

If ct > 0 Then MsgBox "Found " & ct

End Sub

 

[Giovanni03]

Thank you Joe4! it definitely is working except that when it doesn't find and issue (which is ok) it tells me it found 10 instead of found 0. And when it found 1 issue is said found 11.

 

[DanteAmor]

Here another way to search:

Sub highlight()
  Dim dic As Object, c As Range, rng As Range, ct&
  Set dic = CreateObject("Scripting.Dictionary")
  For Each c In Sheets("Sheet 1").Range("M1", Sheets("Sheet 1").Cells(Rows.Count, "M").End(xlUp))
    dic(c.Value) = Empty
  Next
  For Each c In Sheets("Sheet 2").Range("D1", Sheets("Sheet 2").Cells(Rows.Count, "D").End(xlUp))
    If Not dic.exists(c.Value) Then
      If rng Is Nothing Then Set rng = c Else Set rng = Union(rng, c)
      ct = ct + 1
    End If
  Next
  If Not rng Is Nothing Then
    rng.Font.Bold = True
    rng.Font.Color = vbRed
  End If
  MsgBox "Found " & ct
End Sub

 

[Joe4]

Maybe try this:

Sub highlight()

Dim lr As Long, r1 As Range, r2 As Range, cell As Range, ct As Long

With Sheets("Sheet 1")
    lr = .Cells(Rows.Count, "M").End(xlUp).Row
    Set r2 = .Range("M1:M" & lr)
End With
With Sheets("Sheet 2")
    lr = .Cells(Rows.Count, "D").End(xlUp).Row
    Set r1 = .Range("D1:D" & lr)
    For Each cell In r1
        If r2.Find(cell.Value) Is Nothing Then
            cell.Font.Bold = True
            cell.Font.Color = vbRed
            ct = ct + 1
        End If
    Next
End With

If ct > 0 Then MsgBox "Found " & ct

End Sub

Temporarily try this. It will tell you where it is finding each one as it pops up.
You may have some unexpected things going on what you are not aware of.

Sub highlight()

Dim lr As Long, r1 As Range, r2 As Range, cell As Range, ct As Long
ct = 0

With Sheets("Sheet 1")
    lr = .Cells(Rows.Count, "M").End(xlUp).Row
    Set r2 = .Range("M1:M" & lr)
End With
With Sheets("Sheet 2")
    lr = .Cells(Rows.Count, "D").End(xlUp).Row
    Set r1 = .Range("D1:D" & lr)
    For Each cell In r1
        If r2.Find(cell.Value) Is Nothing Then
            cell.Font.Bold = True
            cell.Font.Color = vbRed
            ct = ct + 1
            MsgBox "Found one in cell " & r2.Address
        End If
    Next
End With

If ct > 0 Then MsgBox "Found " & ct

End Sub

 

[Giovanni03]

Here another way to search:

Sub highlight()
  Dim dic As Object, c As Range, rng As Range, ct&
  Set dic = CreateObject("Scripting.Dictionary")
  For Each c In Sheets("Sheet 1").Range("M1", Sheets("Sheet 1").Cells(Rows.Count, "M").End(xlUp))
    dic(c.Value) = Empty
  Next
  For Each c In Sheets("Sheet 2").Range("D1", Sheets("Sheet 2").Cells(Rows.Count, "D").End(xlUp))
    If Not dic.exists(c.Value) Then
      If rng Is Nothing Then Set rng = c Else Set rng = Union(rng, c)
      ct = ct + 1
    End If
  Next
  If Not rng Is Nothing Then
    rng.Font.Bold = True
    rng.Font.Color = vbRed
  End If
  MsgBox "Found " & ct
End Sub

Thank you Dante, I tried your code and it works except that something must be wrong with the range. not sure if its set up to identify everything in column M on sheet 1. Saying this because my entire column (D) on sheet 2 was highlighted.

 

[Giovanni03]

Temporarily try this. It will tell you where it is finding each one as it pops up.
You may have some unexpected things going on what you are not aware of.

Sub highlight()

Dim lr As Long, r1 As Range, r2 As Range, cell As Range, ct As Long
ct = 0

With Sheets("Sheet 1")
    lr = .Cells(Rows.Count, "M").End(xlUp).Row
    Set r2 = .Range("M1:M" & lr)
End With
With Sheets("Sheet 2")
    lr = .Cells(Rows.Count, "D").End(xlUp).Row
    Set r1 = .Range("D1:D" & lr)
    For Each cell In r1
        If r2.Find(cell.Value) Is Nothing Then
            cell.Font.Bold = True
            cell.Font.Color = vbRed
            ct = ct + 1
            MsgBox "Found one in cell " & r2.Address
        End If
    Next
End With

If ct > 0 Then MsgBox "Found " & ct

End Sub

Yea for some reason it keeps showing up the very last line on my sheet 1 and counting those as values. not sure why but basically it counts it 10 times then the one actual red highlighted line.

I deleted the last line and it still pop up but with number 9239

 

[Joe4]

Sorry, there was an error in that line.
It should be:

MsgBox "Found one in cell " & cell.Address

That will tell you all the cells that it is counting, individually, one-by-one.

 

[DanteAmor]

Thank you Dante, I tried your code and it works except that something must be wrong with the range. not sure if its set up to identify everything in column M on sheet 1. Saying this because my entire column (D) on sheet 2 was highlighted.

Do you have formulas in column "D"?

Check the cells, maybe you have blank spaces in the cells.

Also check the values, they may contain blank spaces to the left or right.

Or put a sample of your data here for review. Use the XL2BB tool to paste a minisheet here.

Note XL2BB:​

For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Giovanni03]

Sorry, there was an error in that line.
It should be:

MsgBox "Found one in cell " & cell.Address

That will tell you all the cells that it is counting, individually, one-by-one.

That make sense, its counting all of the extra rows that i have on my file. since I have macros buttons and other things my data doesn't actually start till row 12... good ole facepalm moment for myself. it works perfectly now, I used your original code and made it start count range D12