Conditional formatting multiple rows

Hi there,

I'm sure there isnt much to solving this problem, but im really new to VBA so I'm stuck quite easily.....

Basically I've got a table of monthly budget figures for the whole year (but with some months without data) and i'm creating a macro that highlights a value (green if its less than and Red if its more than) the variable monthly budget.

The Row headings are the months and the column headings are the different areas of spending(eg travel budget, training budget, personnel budget etc)

I can get it working for individual rows, but i cant seem to get the code to repeat in the rows below without getting an error. I'm certain its an v.easy fix but its been bugging me for a while now :S

Here's the code I'm using:

Sub BudgetHighlight()

ActiveCell.Select
Do Until ActiveCell = ""
If ActiveCell > ActiveCell.Offset(-1, 0) Then
ActiveCell.Font.ColorIndex = 3
Else
ActiveCell.Font.ColorIndex = 4
End If
ActiveCell.Offset(0, 1).Select
Loop

<-------I know that these next 2 lines are incorrect and also I know that there is a different way to select the starting cell of the next row without counting like this

ActiveCell.Offset(2, -7).Select
Call BudgetHighlight

End Sub

Any help would be great. Thanks!
Peter

Does this solve your issue? Also, don't forget to wrap CODE tags around the code you post on the forums or the thread will be closed.

Sub BudgetHighlight()
Do
    ActiveCell.Select
    Do Until ActiveCell = ""
        If ActiveCell > ActiveCell.Offset(-1, 0) Then
            ActiveCell.Font.ColorIndex = 3
        Else
            ActiveCell.Font.ColorIndex = 4
        End If
        ActiveCell.Offset(0, 1).Select
    Loop

    ActiveCell.Offset(2, -7).Select
Loop Until ActiveCell = ""
End Sub

Hi Ikaabod,

Thanks alot! That did the job really well, but is there a way to run the loop without having the offset of (2,-7)? Because the entries are done monthly so the offset code would have to be changed all the time to cover the whole table.

I have attached the excel file for a better understanding. Thanks!

Peter

This should do the trick.

Sub BudgetHighlight()
    Dim rng As Range
    Dim iLastCol, iLastRow As Integer
    iLastCol = Range("IV5").Columns.End(xlToLeft).Column
    iLastRow = Range("D65536").Rows.End(xlUp).Row
    i = 3
    
    Do
        i = i + 2
        For Each rng In Range(Cells(i, 4), Cells(i, iLastCol))
            If rng > rng.Offset(-1, 0) Then
                rng.Font.ColorIndex = 3
            Else
                rng.Font.ColorIndex = 4
            End If
        Next rng
    
    Loop Until i = iLastRow
End Sub

Additionally, you could just use the "conditional formatting" under Format --> Conditional Formatting...
Attached is your file using no VBA code whatsoever to achieve this.