VBA formatting with an IF statement

[bbalch]

Hello everyone,

I am trying to create an IF statement using VBA to format the cells in a row (columns A:H) if certain text is found in column A.

The workbook contains account names in Column A. If the name in Column A = "Total Operating Income", "Total Expense", "Total Non-Operating Income", or "Total Income" I would like to apply the format below to the cells in A:H of that row. If the name in Column A does not contain one of the names listed no formatting changes need to be made. The account names in column A start in cell A6 and continue down anywhere from 10 - 150 rows (depending on how many accounts are used by the department).

Range("A:H").Select    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True

Any suggestions on how to incorporate an if statement to format cells based on the text contents found in column A?

Thanks in advance for your help.

 

[Captain_Conman]

Does this do what you are looking for?

Sub FormatColA()


Dim LastRow As Long
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row


For Each C In Range("A6:A" & LastRow).Cells
C.Select
If C.Value = "Total Operating Income" Or C.Value = "Total Expense" Or C.Value = "Total Non-Operating Income" Or C.Value = "Total Income" Then
        C.Offset(0, 7).Select
        Range(Selection, Selection.End(xlToLeft)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Else
    End If
    Next
End Sub

 

[Scott Huish]

No need to Select. I would also clear the formats at the beginning in case the data changes the next time the macro is run. You can also simplify the if by using Select Case.

Sub FormatColA()
Dim LastRow As Long, c As Range
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Range("A:H").ClearFormats
For Each c In Range("A6:A" & LastRow)
    Select Case c
        Case "Total Operating Income", "Total Expense", "Total Non-Operating Income"
            With c.Resize(, 7).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.249977111117893
                .PatternTintAndShade = 0
            End With
    End Select
Next
End Sub

 

[Captain_Conman]

Thanks for the advice Scott Huish!

I am new to this, but think it is fun to try to find answers to these problems. It is a great way to learn excel/VBA a lot better as well.

Thank you!

 

[bbalch]

Thank you both! I tried Scott's VBA and that did the trick.