Changing multiple cells not in a range

[jplmac]

Hi,

Is there a way to select multiple cells that would not make up a range or be resized e.g. 'B2' & 'D2'?

To explain, I have written an event handler macro, which will set the cell colour to grey for other cells in the row. The cells it will change will vary depending on what is filled in for column A (from a validation list). Because these cells are in non-adja

cent columns (e.g. B & D) I can't use range or resize, so I have to repeat With End With for each cell, meaning there is a lot of code.

I have only included part of the macro as an example.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
    Case 1
    
        ThisRow = Target.Row
    
        Select Case Target
            Case "Renamed"
                With Cells(ThisRow, 2).Interior
                    .Pattern = xlSolid
                    .TintAndShade = -0.249977111117893
                End With
                With Cells(ThisRow, 4).Interior
                    .Pattern = xlSolid
                    .TintAndShade = -0.249977111117893
                End With

            Case "Deleted"
                With Cells(ThisRow, 3).Interior
                    .Pattern = xlSolid
                    .TintAndShade = -0.249977111117893
                End With
                With Cells(ThisRow, 7).Interior
                    .Pattern = xlSolid
                    .TintAndShade = -0.249977111117893
                End With

        End Select
End Select
End Sub

 

[wigi]

I think that conditional formatting might be a worthwile alternative to look at.

If yoiu stick with VBA code, use a second procedure containing the code for 1 cell. Pass parameters to that cell and execute it (call the procedure) from the main event code.