CODEWITHSUNDEEP
excelvba
Samuel Meddows
Samuel Meddows

Reputation: 36712

Excel - Running IF/ELSE logic on user selection

I want the run an IF/ELSE statement across the range of cells selected by the user. Currently which ever IF/ELSE statement is true for the first cell in the selection, is then applying to all the cells in the section rather than doing a for each loop.

Sub MultiCellTest()
  With Selection
    If ActiveCell > 10 Then
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 6684927
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Else
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 16764159
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
End With
End Sub

Thanks.

Upvotes: 0

Views: 40

Answers (1)

braX
braX

Reputation: 11755

Here is how you would modify your code to handle each cell in the active selection.

Sub MultiCellTest()
  For Each cell in Selection
    If cell > 10 Then
        With cell.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 6684927
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Else
        With cell.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 16764159
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
  Next
End Sub

Upvotes: 1

Related Questions