Coloring a range of cells (VBA)

Question

I have a code that checks a specific column. If that particular column (col 21) is FALSE, then I would like to color only cells in columns 8, 9 and 10 in the that particular row where there is a false.

For i = 9 To LastRow
     If Sheets("Test").Cells(i, 21).Value = "False" Then
     Sheets("Test").Cells(i, 8:10).Font.Color = RGB(255, 0, 0)
     End If
Next i

This does not work. Is there a way to do this in one line, rather than coloring each cell at once?

Thanks

Any specific reason to avoid Conditional formatting which is way faster and native to Excel? — jainashish
I am not sure if Conditional Formatting can do what I want. I would like to highlight the 3 columns (cols 8, 9 and 10) irrespective of what they contain, only by what col21 contains. — Oday Salim
You can definitely achieve it through conditional formatting. If you wish, I can provide the answer. — jainashish
Go on - I thought cond. format. depends on what is in cell.. — Oday Salim

paul bica paul bica · Accepted Answer · 2018-06-03T19:08:01

To fix your code update this line: ws.Range(ws.Cells(r, 8), ws.Cells(r, 10)).Font.Color = vbRed

Option Explicit

Public Sub ShowFalse()
    Dim ws As Worksheet, lr As Long, r As Long

    Set ws = ThisWorkbook.Worksheets("Test")
    lr = ws.Cells(ws.Rows.Count, 21).End(xlUp).Row

    If lr > 8 Then
        Application.ScreenUpdating = False
        For r = 9 To lr
            If Not IsError(ws.Cells(r, 21)) Then
                If ws.Cells(r, 21).Value = False Then
                    ws.Range(ws.Cells(r, 8), ws.Cells(r, 10)).Font.Color = vbRed
                End If
            End If
        Next
        Application.ScreenUpdating = True
    End If
End Sub

To make it faster use AutoFilter

Public Sub ShowFalseAF()
    Dim ws As Worksheet, lr As Long, fCol As Range, clrCols As Range

    Set ws = ThisWorkbook.Worksheets("Test")
    lr = ws.Cells(ws.Rows.Count, 21).End(xlUp).Row

    If lr > 8 Then
        Set fCol = ws.Range(ws.Cells(9, 21), ws.Cells(lr, 21))
        Set clrCols = ws.Range(ws.Cells(9, 8), ws.Cells(lr, 10))

        Application.ScreenUpdating = False
        fCol.AutoFilter 1, False
        If fCol.SpecialCells(xlCellTypeVisible).Count > 1 Then
            ws.Rows(9).Hidden = ws.Cells(9, 21) <> False
            clrCols.Font.Color = vbRed
            ws.Rows(9).Hidden = False
        End If
        fCol.AutoFilter
        Application.ScreenUpdating = True
    End If
End Sub

Coloring a range of cells (VBA)

2 Answers