Macro to affect cell background color

[jmk15315]

Good morning.

I have a spreadsheet that has a column for a date after an entry of a price. I want to create a macro that will color the cell red (or a shade of red) if the value of a specific cell is true. I have the following code that works, but I would like to know if there is a way to apply this to a range of cells, without having to create an IF/Else statement for each cell I want to affect. This is what I have currently.

    If Range("S3").Value = True Then
        Range("U3").Select
        Selection.Font.Bold = True
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.399975585192419
            .PatternTintAndShade = 0
        End With
    Else
        Range("U3").Select
        Selection.Font.Bold = False
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.399975585192419
            .PatternTintAndShade = 0
        End With
    End If

End Sub

The cells I want to change, based on the based on the TRUE/FALSE value in the corresponding rows Column "S" are ("U3:U8,U10:U12,U14:U41")

I would guess there is a way to accomplish this but I am at a loss. Any assistance would be welcomed

 

[mumps]

Try:

Sub ColorCells()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("U3:U8,U10:U12,U14:U41")
        If rng.Offset(, -2).Value = True Then
            rng.Font.Bold = True
            With rng.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent2
                .TintAndShade = 0.399975585192419
                .PatternTintAndShade = 0
            End With
        Else
            rng.Font.Bold = False
            With rng.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.399975585192419
            .PatternTintAndShade = 0
        End With
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[jmk15315]

Added an End If before "Next rng", but works perfectly. Thanks for your assistance.

 

[mumps]

Oops!!! Thanks for catching that and you are very welcome.