Worksheet Comparison

[Guest]

I have a small family business and need to be able to compare cells from
sheet1 to sheet2 via macro. Each worksheet has a different number of rows
and columns. Conditional formatting would be great but not allowed with
multiple sheets. "Range" seems appropriate but I'm not familiar with it. In
short of what I'm trying to accomplish, I need to highlight the cells from
sheet1 that matches the header from sheet2. Multiple results are expected
from each row/column on sheet1. Can you help? I would like all users to be
able to run a macro montly to validate data collected.
Hope you can help me. Thanks!

 

[Guest]

Please supply more specific information on what you want?

Is the header in one row or across many rows.
Does the header need to match the data in the same row?

 

[Guest]

Sorry about that.

The header is in one row.

The header from sheet1 will never be a header in sheet2 and vise versa.

Sheet1 header may be in multiple cells in different rows and/or columns on
sheet2.

I hope this is clearer. Working this through has been a challenge today.
Thanks again.

 

[Guest]

I found assistance from one of the discussions but I'm still at a lost on how
I can use another worksheet (from the same workbook) as the source for
"MySearch" below.
Sheet2 ("WORK") ROW1 contain all the information I need in my array
"MySearch".

Private Sub CompSource_Click()
Dim FirstAddress As String
Dim MySearch As Variant
Dim myColor As String
Dim rng As Range
Dim I As Long
Dim ProData As Integer

MySearch = Array("07JA07", "JNE07", ...) *** this is only part of the data
that "HOME" Row 1 has. It has the potential to grow to 100 rows. Once it
reaches "" end search ***
myColor = "6"
With Sheets("HOME").Cells 'SHEET1

..Interior.ColorIndex = xlColorIndexNone

For I = LBound(MySearch) To UBound(MySearch)
Set rng = .Find(What:=MySearch(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
FirstAddress = rng.Address
Do
rng.Interior.ColorIndex = myColor
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> FirstAddress
End If
Next I
End With
End Sub

Hopefully this gives you an idea of what I'm working on.