VBA Can 'with' statement cover several ranges or objects?

user_7994509 0
Viewed 69

Just wanted to know if the following code could be simplified without adding an array or a loop.

Variables Cell1, Cell2 & Cell3 are highly independent. That means they won't be the same every time, as they will come from several previous calculations.

    With Rng
        With .Columns(Cell1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        With .Columns(Cell2).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        With .Columns(Cell3).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With

Is there a way to write something like:

    With Rng
        With .Columns(Cell1).Interior, .Columns(Cell2).Interior, .Columns(Cell3).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With

Thanks,

vba excel
1 Answers

You could use Union.

    With Rng
        With Union(.Columns(Cell1), .Columns(Cell2), .Columns(Cell3)).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With
user_28500260
Related