Help Tweaking My Code

[Dazzawm]

The code listed below is a macro to highlight what is on sheet 2 but not on sheet 1 within a workbook. Because there is a lot of data to look at it is taking about an hour to finish!

If the code was tweaked to look at a specific column on sheet 1 that I can change everytime would this speed it up?

If so, could someone amend the code to say look at column 'H' in this instance.

Sub dazzawm()
Dim c As Range, Found As Range
With Sheets("Sheet2")
For Each c In .UsedRange
Set Found = Sheets("Sheet1").UsedRange.Find(what:=c.Value)
If Found Is Nothing Then c.Interior.ColorIndex = 3
Next c
End With
End Sub

 

[mirabeau]

UsedRange is often a rather inefficient way of looking at things.

Anyway, maybe if you modify your code to

Sub dazzawm()
Dim c As Range, Found As Range
With Sheets("Sheet2")
For Each c In .UsedRange
Set Found = Sheets("Sheet1").Range("H:H").Find(what:=c.Value)
If Found Is Nothing Then c.Interior.ColorIndex = 3
Next c
End With
End Sub

 

[Dazzawm]

So where is says H:H in your code I just change that everytime?

 

[GTO]

Hi there,

Why are we looking all over sheet1 for every used cell in sheet2? Is the data really layed out so much differently between the two sheets that we couldn't let's say, find a header? Just curious; as you noted, looking all over for ea cell is going to be slow.

Mark

 

[mirabeau]

So where is says H:H in your code I just change that everytime?

Not clear what you mean by "everytime". You said column H so I modified your code to column H.

If you've got some other criterion or criteria for deciding on which column other than just the flat statement that it's column H then ... ???

Edited:
Maybe I've got an idea what you're getting at.
I'll consider.

 

[Dazzawm]

Not clear what you mean by "everytime". You said column H so I modified your code to column H.

If you've got some other criterion or criteria for deciding on which column other than just the flat statement that it's column H then ... ???

I did say in this instance column 'H' as I have a master workbook which I copy into every other workbook as sheet 2, and as we all know different worksheets need to look at different criteria so on another workbook it may need to look at column 'D'

 

[mirabeau]

I did say in this instance column 'H' as I have a master workbook which I copy into every other workbook as sheet 2, and as we all know different worksheets need to look at different criteria so on another workbook it may need to look at column 'D'

And so i really don't have much idea what you're looking for with your post.

So I tried your code on some test data to see what it did, which was (I supposed) what you were looking for, except that you apparently thought it too slow.

I really don't much like having to do things this way, a good and clear initial explanation usually helps a lot towards getting useful responses.

Anyway, here's some code which produces the same result as yours, but does so quite a bit faster.

Sub dazzawnanew()
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

If this isn't useful to you, maybe you can just change Column(HH) every time.

 

[Dazzawm]

And so i really don't have much idea what you're looking for with your post.

Basically all I asked was for it amended to look at a specific column rather than the entire worksheet to speed it up. I will try your new code, thanks.

 

[mirabeau]

Basically all I asked was for it amended to look at a specific column rather than the entire worksheet to speed it up.

So that was what you got.

Was there some problem?

 

[Dazzawm]

So that was what you got.

Was there some problem?

No, but you made it out to be some big deal by saying 'I really don't much like having to do things this way, a good and clear initial explanation usually helps a lot towards getting useful responses'.

I appreciate your help but why get involved if you don't like having to do things this way?