Excel - lots of conditional formatting converted to VBA

Question

For an Excel spreadsheet order form, I need a way to apply conditional formatting to all rows with one macro, from row 78 down until there is no more data.

Each column has its own conditional formatting formulas. Some have multiple formulas. I tried using the Record Macro function, but since there is so much going on, the resulting VBA code is messy, and I'm not sure how to combine it all.

I don't need someone to write all the code for me, but I'm hoping I can get a little guidance to figure out how to do all of it.

There are about 15 columns that need conditional formatting applied to them. Here are a few columns to show what I'm working with:

A78:

Formula: =AND($A$78="",COUNTA(78:78)>=1) | white text, red fill | Stop If True

C78:

Format only cells that contain > Specific Text > beginning with > M | no format | Stop If True

Format only cells that contain > Specific Text > beginning with > F | no format | Stop If True

Format only cells that contain > No Errors | red background, white text

D78:

Cell value is greater than 300

Answer 1

You can do this fairly easily with a DO-WHILE Loop. I'll give a start for "D78" and you should be able to finish the rest.

sub formatCells()

    Dim count as Integer
    Range("D78").Activate

    count = 0
    Do While ActiveCell.Offset(count, 0).Value <> ""
        If ActiveCell.Offset(count, 0).Value > 300 Then
            'Do Stuff
        End If
        count = count + 1
    Loop
End Sub

Answer 2

You need to create a FormatCondition object for each rule you want to set. Here are the basics for setting up a formula-based conditional format.

'Set a variable for the formatcondition to make it easier to work with.
Dim fc As FormatCondition

'Create the formatcondition.
strFormula = "=$A1=$B1"
Set fc = Range("A:A").FormatConditions.Add(Type:=xlExpression, Formula1:=strFormula) '(There is a "Formula2" property that only applies if you are using one of the built-in conditional formatting rule types. It does not apply if you are using an xlExpression rule type).

'Move it to the top of the list (optional).
fc.SetFirstPriority

'Set "Stop if True" (optional).
fc.StopIfTrue = True

'Set interior Color (optional).
fc.Interior.Color = RGB(255,0,0) 'red

'Set borders (optional).
arBorders = Array(xlLeft, xlRight, xlTop, xlBottom)
For Each borderConst In arBorders
    fc.Borders(borderConst).LineStyle = xlContinuous
Next

'Set font (optional).
fc.Font.Italic = True
fc.Font.Bold = True
fc.Font.Underline = True

I would recommend setting up a procedure as below to simplify this process. I created this one for my own use. It can only set borders and fill color, but could be modified to set font attributes, etc.

Sub AddFormatCondition(rgAppliesTo, strFormula, Optional bSetFirstPriority, Optional FillColor, Optional bBorders, Optional bStopIfTrue)

    Dim fc As FormatCondition
    Set fc = rgAppliesTo.FormatConditions.Add(Type:=xlExpression, Formula1:=strFormula)
    If Not IsMissing(bSetFirstPriority) Then
        If bSetFirstPriority Then fc.SetFirstPriority
    End If
    If Not IsMissing(FillColor) Then
        With fc.Interior
            .Color = FillColor
        End With
    End If
    If Not IsMissing(bBorders) Then
        If bBorders <> 0 Then
            arBorders = Array(xlLeft, xlRight, xlTop, xlBottom)
            For Each borderConst In arBorders
                fc.Borders(borderConst).LineStyle = xlContinuous
            Next
        End If
    End If
    fc.StopIfTrue = bStopIfTrue
End Sub