Looping through cells and applying conditional formatting

Hi,

My problem is that we work with sets of data in Excel, and I would like to automate the checking/verification by highlighting cells that are blank, or should not be blank, or do not meet various criteria.

I had successfully written code that worked on a single column, going down the rows, but expanding this to multiple columns has proved tricky.

Because the datasets vary hugely in length and width with potentially missing bits, empty columns, etc, I am providing input boxes for the user to manually indicate the last row & last column, rather than automatic detection. The only constant is that the data begins in cell A4.

Attached is a sample data file. The code below simply asks for the last row, last column, then is supposed to loop through the range of cells, checking each time whether the value in the first row for that column is a 1, 2 or 3. This value will dictate the type of formatting to be applied, as different columns need checked for different things. For now, I'm just keeping it simple and using one value, which is just intended to highlight any blank cells.

But it doesn't work, and I can't see why not. All this code does is fill the currently selected cell with red.

Sub FormatTheSheet()

Dim iRowLast As Integer
Dim strColLast As String


    iRowLast = Application.InputBox(Prompt:="Please enter the row number of the last row of data. Enter '0' to abandon the formatting.", _
        Title:="LAST ROW", Type:=1)
    On Error Resume Next

    strColLast = Application.InputBox(Prompt:="Please enter the column reference of the last column of data. Type 'quit' to abandon the formatting.", _
        Title:="LAST COLUMN", Type:=2)
    On Error Resume Next
     
Dim iCell As Range
Dim Header As Range
  
For Each iCell In Range("A4:" & strColLast & iRowLast)

    Header = Cells(1, ActiveCell.Column)
    If Header = 1 And IsEmpty(ActiveCell) Then
        ActiveCell.Interior.ColorIndex = 3
    End If
    
    ' insert code for if Header = 2
    ' insert code for if Header = 3
    
Next
    
End Sub

Obviously I will expand the for .. each loop with different header codes once the mechanism is working correctly, but I can't see why it shouldn't at least check through the given range.

Any help greatly appreciated.

PS. This is in Excel 2000. And if the macro would work as intended, I would expect cell D7 in sample.xls to be highlighted in red as it is missing.