Macro to format cells based on whether or not their contents are in a list on another tab

[tcorbs]

So I have a range of cells, specifically A1:LH1100 on Sheet 2. Some of these cells are blank, some contain an ID number.
On Sheet 1, I have a dataset which contains ID Numbers in column A, and other descriptive fields in columns B-E

I am lookking for a macro that will go through each cell in that range on Sheet 2 (A1:LH1100) and perform the following:

-if the cell is empty, go to next cell
-if the cell contains an ID number
-if the ID number is on the list on Sheet 1, format the cell to be filled "red"
-if the ID number is not on the list on Sheet 1, format the cell to be filled "blue"

Ideally I would like to easily be able to expand this and format based on whether that cell ID is not only listed on Sheet 1, but also meets other criteria (in columns B-E)

I was thinking I could do some sort of "if ( sumifs" formula in VBA but I'm not sure. The code that I was trying was a For Each loop and it looked like it would work for about the first 100 rows and then stop, though none of those cells would be formatted any differently. So I am thinking I may have been way off.

Please help. Thanks!

 

[JoeMo]

Are there any formulas on Sheet2 in the range A1:LH1100?

 

[tcorbs]

Are there any formulas on Sheet2 in the range A1:LH1100?

No those are all strings like "101A13"

 

[tcorbs]

And they are all values in the list on Sheet 1, which is about 25000 rows long

 

[tcorbs]

This is my code so far...which is not working

Sub All_Sold()


Dim all_cells As Range
Dim cell As Range

Set all_cells = Sheets("Sheet 2").Range("A1:LH1100")

For Each cell In all_cells


If cell.Text <> "" Then
If Application.WorksheetFunction.CountIfs(Sheets("Sheet 1").Range("A1:A25000"), cell.Text) > 0 Then cell.Interior.ColorIndex = 3 Else
cell.Interior.ColorIndex = 6
End If

Next


End Sub

 

[tcorbs]

Nevermind...I'm an idiot. Code was working but I had conditionally formatted the cells such that I wasn't seeing any results. I apologize to anyone whose time I wasted with this.

I guess the lesson to be learned here is that if there is no error message, it is often because there is no error. Thanks