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

 

[Dazzawm]

I forgot to add I have done copy>pastespecial>values etc.. just in case.

 

[Dazzawm]

^

 

[mirabeau]

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

Hi,

Do you mean that the highlighting appears on both sheets?

And (assuming the code can be made OK otherwise) is it only numbers (specifically integers) that you are interested in, or may you want text highlighted as well?

Specifically on the code you post, problem may possibly arise from the particular use of the scripting dictionary object. The way it's used there may fall down in some circumstances.

A modification of the above code that might work for you is:
replace the lines in red by

For Each e In a1
    If Not d.exists(e) Then d.Add e, Empty
Next e

For i = 1 To LR2: For j = 1 To lc2
    If Not d.exists(a2(i, j)) Then _
        Sheets("sheet2").Cells(i, j).Interior.ColorIndex = 3

If the wrong elements are highlighted, try deleting the "Not" in red and see how that goes.

 

[Dazzawm]

No, the code highlights only on sheet 2 what is on that sheet and not on sheet 1. What it looks for is anything, text, integer, mixture of both etc.. The code I posted has been problem free for ages so I don't understand why it is playing up now. It is specifically numbers it is doing it on.

 

[Dazzawm]

Tried what you suggested and it is still doing it.

 

[mirabeau]

I can't see that there's anything about with code that might cause that sort of problem.

Are the numbers indeed the same?

Are both of them numbers and not (say) one is the result of some formula.

Do they both format the same of you copy and paste to a new sheet?

Can you do arithmetic operations equally well with both? Like do they add together OK to give the expected result.

Without seeing an example of the situation you describe, and not knowing how your data are generated for each worksheet (I don't want to know) it's hard to be more specific.

I came across a situation a week or so back on this forum where I solved the OP's problem for the data as it was posted here. But there was seemingly a problem when the method was applied to the original data, which seemingly had some features that were filtered out by Jeannie or whatever posting process was used.

 

[Dazzawm]

The numbers are a result of a Vlookup which is why I paste specialed values etc. Although most if not all do have a little green mark in the corner of the cell.

 

[mirabeau]

Click on a cell with the small green triangle.

There should appear next to it a small box with exclamation mark in it.

Click on that and note the options. They may tell you what the cause of the problem is.

e.g. if it's "number stored as text" you may then have the option of converting this to number. Up to you whether you want to do this or not.

 

[Dazzawm]

Click on a cell with the small green triangle.

There should appear next to it a small box with exclamation mark in it.

Click on that and note the options. They may tell you what the cause of the problem is.

e.g. if it's "number stored as text" you may then have the option of converting this to number. Up to you whether you want to do this or not.

Thanks, that done it. I selected them all then converted to number and it worked.