Compare Numbers and highlight

Hello:

Please refer to attached file.
I need a VB Code to perform the following
I have numbers in Sheet1 from B6 down for 4 rows (This number is in Cell B2)..If B2 is 8 then data will be from B6 down for 8 rows
I need to check each of these numbers in Column C of Sheet2.
If this number (Sheet1) is not found then highlight the cell to Red (Sheet1 bewtee B6 down).
Would need to clear the highlight from B6 down before we start the test.


Let me know if you have any questions.
Thanks.
RM

Hi RM,
try

Sub ertert()
Dim x, i&
With Sheets("Sheet1")
    With .Range("B1", .Cells(Rows.Count, 2).End(xlUp))
        .Interior.Color = xlNone: x = .Value
    End With
    For i = 6 To UBound(x)
        If Sheets("Sheet2").Columns(3).Find(x(i, 1), LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then .Cells(i, 2).Interior.ColorIndex = 43
    Next
End With
End Sub

Hi Nilem:

This works soooooooooo great.
Need to see if I can compare Sheet1 data with closed workbook.
The name of the sheet where the data is will be the same as name of the closed workbook.
Example : Name of the closed workbook is Store1Data.xls
Then, the name of the sheet in closed workbook where the data is will be "Store1Data"
The data to be compared will still be in Column C of closed WB
Name and Location of the closed upbook will be in Cell S3 of the current workbook.

Thanks a lot and Please let me know if you have any questions.
Thanks.
RM

Sub ertert()
Dim x, i&, wsh As Worksheet
Set wsh = ThisWorkbook.Sheets("Sheet1")
With wsh.Range("B1", wsh.Cells(Rows.Count, 2).End(xlUp))
    .Interior.Color = xlNone: x = .Value
End With
Application.ScreenUpdating = False

With GetObject(Range("S3").Value)
    With .Sheets("Store1Data").Columns(3)
        For i = 6 To UBound(x)
            If .Find(x(i, 1), LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then wsh.Cells(i, 2).Interior.ColorIndex = 43
        Next
    End With
    .Close 0
End With
Application.ScreenUpdating = True
End Sub

it is assumed that the S3 cell has value something like E:\Downloads\Store1Data.xls

Hi Nilem:

Superb, Works great,.
Thanks a lot and will test further and let you know if any issues.

Riz