Looping through cells and applying conditional formatting

**stroberaver** · 01-07-2011, 09:53 AM

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.

**Bob Phillips** · 01-07-2011, 11:09 AM

Not tested but I did notice something

Sub FormatTheSheet()
Dim iRowLast As Integer
Dim strColLast As String
Dim iCell As Range
Dim Header As Range

    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

    For Each iCell In Range("A4:" & strColLast & iRowLast)
    
        Header = Cells(1, ActiveCell.Column)
        If Header = 1 And IsEmpty(ActiveCell) Then
        
            iCell.Interior.ColorIndex = 3
        End If
        
        ' insert code for if Header = 2
        ' insert code for if Header = 3
    Next
End Sub

**stroberaver** · 01-07-2011, 12:10 PM

Thanks! That little tweak gets the range looping working correctly, but it's just filling every cell with red, regardless of the contents of "Header" or whether the cell is actually blank.

I've tried replacing the ActiveCell references inside the loop with iCell references, but it performs just the same. Somehow, the bit of code below is qualifying every cell as blank and with header = 1.

Header = Cells(1, iCell.Column)
        If Header = 1 And IsEmpty(iCell) Then
        
            iCell.Interior.ColorIndex = 3
        End If

**Bob Phillips** · 01-07-2011, 01:47 PM

I see the problem, you have wrongly declared Header

Sub FormatTheSheet()
Dim iRowLast As Integer
Dim strColLast As String
Dim iCell As Range
Dim Header As Long

    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

    For Each iCell In Range("A4:" & strColLast & iRowLast)
    
        Header = Cells(1, iCell.Column)
        If Header = 1 And IsEmpty(iCell) Then
        
            iCell.Interior.ColorIndex = 3
        End If
        
        ' insert code for if Header = 2
        ' insert code for if Header = 3
    Next
End Sub

but you can also do it without prompting the user

Sub FormatTheSheet()
Dim iCell As Range
Dim Header As Long

    For Each iCell In Range("A4").CurrentRegion
    
        Header = Cells(1, iCell.Column)
        If IsEmpty(iCell) Then
        
            Select Case Header
            
                Case 1: iCell.Interior.ColorIndex = 3
                Case 2: ' insert code for if Header = 2
                Case 3: ' insert code for if Header = 3
            End Select
        End If
    Next
End Sub

**stroberaver** · 01-08-2011, 11:01 PM

Originally Posted by Bob Phillips

I see the problem, you have wrongly declared Header

Brilliant, thank you. This now works correctly.

I must confess I don't entirely understand this change though. I defined "Header" as a range because I thought it was a cell reference, but I get the impression it needs to be set as Long because of what it contains, rather than what it is?

Originally Posted by Bob Phillips

but you can also do it without prompting the user

Thank you, although as mentioned I'm deliberately leaving this to manual specification since the data may often contain blank rows or columns where records or fields are missing, and I don't wish to rely on Excel to decide where the data ends.

However I do like the select case method of processing the "Header" value, many thanks for reminding me about this - more efficient than all the "if" statements I was planning!

**Bob Phillips** · 01-09-2011, 07:17 AM

Originally Posted by stroberaver

Brilliant, thank you. This now works correctly.

I must confess I don't entirely understand this change though. I defined "Header" as a range because I thought it was a cell reference, but I get the impression it needs to be set as Long because of what it contains, rather than what it is?

Header is just a variable, and you could declare it as a range object and set it to the cell itself

Set Header = Cells(1, iCell.column)

but then you would refer to Header.Value in the code, not just Header.

Your code at present is actually setting Header to the cell Value not the cell itself. Value is the default property, so it can be omitted, but this is a great example of why you shouldn't assume defaults. So it is better to write