Help With On Sheet 2 Not On Sheet 1 Code

[Dazzawm]

I have the code below that has worked fine for some time but now it is causing me problems on a certain WB. It highlights in red what is on sheet 2 but not on sheet 1. I am using it now but it is highlighting certain numbers that are on both sheets. These come in the form of years or 4 digit numbers i.e 1997, 2008 etc..

It only does it with some and not all. Can someone help please.

Sub OnSheet2NotSheet1()
Dim LR1&, lc1&, LR2&, lc2&
Dim d As Object, a1, a2, e, i&, j&
With Sheets("sheet1")
LR1 = .Cells.Find("*", After:=.Cells(1), SearchOrder:=xlByRows, _
    searchdirection:=xlPrevious).Row
lc1 = .Cells.Find("*", After:=.Cells(1), SearchOrder:=xlByColumns, _
    searchdirection:=xlPrevious).Column
a1 = .Cells(1).Resize(LR1, lc1)
End With
With Sheets("sheet2")
LR2 = .Cells.Find("*", After:=.Cells(1), SearchOrder:=xlByRows, _
    searchdirection:=xlPrevious).Row
lc2 = .Cells.Find("*", After:=.Cells(1), SearchOrder:=xlByColumns, _
    searchdirection:=xlPrevious).Column
a2 = .Cells(1).Resize(LR2, lc2)
End With
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1
For Each e In a1: d(e) = 1: Next e
For i = 1 To LR2: For j = 1 To lc2
    If d(a2(i, j)) <> 1 Then _
        Sheets("sheet2").Cells(i, j).Interior.ColorIndex = 3
Next j, i
End Sub

 

[mirabeau]

OK. Good that all seems to work now.

You can do that conversion as a batch (whole lot at once) so as don't have to do it one-by-one.

Also, there's another current thread on comparing numerical values on this forum right now which may be of some interest/use. http://www.mrexcel.com/forum/showthread.php?t=562972

 

[Dazzawm]

OK. Good that all seems to work now.

You can do that conversion as a batch (whole lot at once) so as don't have to do it one-by-one.

Also, there's another current thread on comparing numerical values on this forum right now which may be of some interest/use. http://www.mrexcel.com/forum/showthread.php?t=562972

You say there is a way of doing them in a batch. How? I did it by having to drag the mouse down 30000 rows as selecting the column the ! disappears.

 

[mirabeau]

You say there is a way of doing them in a batch. How? I did it by having to drag the mouse down 30000 rows as selecting the column the ! disappears.

Probably no general solution for all problem cases with a green triangle.

But in your case it could probably be done manually by:
Enter 1 in a spare cell -> copy -> select range to convert -> paste special(multiply)

Or maybe in VBA by doing

Set a = (range to convert)
a.Value = a.Value