CODEWITHSUNDEEP
vbaexcel
Oday Salim
Oday Salim

Reputation: 1147

Coloring a range of cells (VBA)

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

Upvotes: 2

Views: 4246

Answers (2)

jainashish
jainashish

Reputation: 5203

Following our discussion in the comments, follow the steps below to apply conditional formatting in MS Excel on selected cells, range or column based on another column's value.

Step #01. Select the range(8:10 - H:J) in which you want to apply formatting.

Step #02. Click on 'Conditional Formatting' in the Home tab.

Step #03. Click on 'New Rule' and then select 'Use a formula to determine which cells to format'

Step #04. Provide the formula below 

=OR($U1=FALSE,$U1="FALSE")

Step #05. Click on Format to apply desired formatting and then click OK.

enter image description here

Upvotes: 3

paul bica
paul bica

Reputation: 10705

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

Upvotes: 3

Related Questions