Cell comparison multiple sheets

[mbpress01]

I have a few sheets that I need to compare cells in one column in sheet1 vs one column in sheet2. If match is made (with an "alert" match also) , the code colors the cell. The code taken from this site is as follows:

Sub Compare()

Dim w1 As Worksheet
Dim w2 As Worksheet
Dim c As Range
Dim a As Range

Set w1 = Sheets("sheet1")
Set w2 = Sheets("sheet2")

With w1
   For Each c In .Range("A1:A100")
   Set a = w2.Columns(1).Find(c.Value, LookAt:=xlWhole)
   If Not a Is Nothing Then
      If w1.Cells(c.Row, 1).Value <> w2.Cells(a.Row, 1).Value And .Cells(c.Row, 2) = "ALERT" Then
         w2.Cells(a.Row, 1).Interior.ColorIndex = 3
      End If
   End If
  Next c
End With

End Sub

As you can see, the goal is to run a comparison on column 1 sheet1 against column 1 in sheet2. It works perfectly but what I need to do is to run the same comparison against sheet 3, 4, 5 6, etc etc. I know there is a way to do this through an array but I can't figure it out. I can copy the code multiple times and just change the w2 reference to w2 = sheet3, w2 = sheet4 etc but that is inefficient. I am not clear how to loop the array for each sheet.

Thanks for the help in advance and this site has been hugely helpful in my daily work.

 

[Fluff]

Not sure if I'm missing something, but I don't see how that code can work.
You find the value from col A sheet1 in col A sheet2 & then test to see if they are not the same. Doesn't sound quite right to me.

 

[mbpress01]

Hi Fluff, I changed a few things around but maybe I messed up the way the macro works. The below does work.

Sub Compare()
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, a As Range
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
With w1
  For Each c In .Range("A2:A100")
    Set a = w2.Columns(1).Find(c.Value, LookAt:=xlWhole)
    If Not a Is Nothing Then
      If .Cells(c.Row, 6).Value <> w2.Cells(a.Row, 6) Then
        w2.Cells(a.Row, 6).Font.Color = vbRed
      End If
    End If
  Next c
End With
End Sub

Sheet 1 Data

Test.xlsm
	A	B	C	D	E	F	G
1	User	Id	email addy	Active	street	Product code	Client
2	AB123	tom	email addy	Yes	x	5061350	BP
3	CD234	****	email addy	Yes	y	1010527918	BA
4	DF345	harry	email addy	No	z	1000191554	Shell
5	FG456	John	email addy	Yes	v	1010209781
Sheet1

Sheet 2 looks like this after i run the code:

Test.xlsm
	A	B	C	D	E	F	G
1	User	Id	email addy	Active	street	Product code	Client
2	AB123	tom	email addy	Yes	x	5061350	BP
3	CD234	****	email addy	Yes	y	4509980011	BA
4	DF345	harry	email addy	No	z	1000191554	Shell
5	FG456	John	email addy	Yes	v	983488567	AA
Sheet2

Any help appreciated as my goal is to have this work exactly the same way but add an "and" condition, which I can do, and apply the same logic to multiple sheets without having to copy the code 6 times as all i would substitute is sheet2 for sheet3, sheet4, etc etc. This I can't do.

Thanks again.

 

[Fluff]

Do you still need to compare col F?

 

[mbpress01]

Yes. I would need to compare the F in each and every sheet if that makes sense.

 

[Fluff]

So if col A matches & col F doesn't & col B has "ALERT" then highlight col A. Is that correct?

 

[mbpress01]

Exactly. That is what I want to do but use the same code for sheet2, sheet3 etc.

Thx so much for the help.

 

[Fluff]

How about

Sub Compare()
Dim w1 As Worksheet, Ws As Worksheet
Dim c As Range, a As Range
Set w1 = Sheets("Sheet1")
For Each Ws In Worksheets
   If Ws.Name <> w1.Name Then
      For Each c In w1.Range("A2:A100")
         Set a = Ws.Columns(1).Find(c.Value, LookAt:=xlWhole)
         If Not a Is Nothing Then
            If w1.Cells(c.Row, 6).Value <> Ws.Cells(a.Row, 6) And c.Offset(, 1) = "ALERT" Then
               a.Interior.Color = 3
            End If
         End If
      Next c
Next Ws
End Sub

 

[mbpress01]

Very interesting and this is great. But what happens if I have 15 sheets but I want the procedure to go from sheet2 to sheet10 only. That is the crux of the question and why i thought an array with the sheet names would work but I don't know how to program.

Any thoughts appreciated.

 

[Fluff]

That is the crux of the question

Then why did you not say at the start?
You said you wanted to run on sheets 2,3,4,5,6 etc etc which gives the definite idea it is for all sheets. At no point did you mention specific sheets.

That said, try

Sub Compare()
Dim w1 As Worksheet, Ws As Worksheet
Dim c As Range, a As Range
Set w1 = Sheets("Sheet1")
For Each Ws In Sheets(Array("sheet2", "sheet3", "Sheet4"))
   For Each c In w1.Range("A2:A100")
      Set a = Ws.Columns(1).Find(c.Value, LookAt:=xlWhole)
      If Not a Is Nothing Then
         If w1.Cells(c.Row, 6).Value <> Ws.Cells(a.Row, 6) And c.Offset(, 1) = "ALERT" Then
            a.Interior.Color = 3
         End If
      End If
   Next c
Next Ws
End Sub