Solved: Change Cell Fill Colour

[hobbiton73]

Hi, I wonder whether someone could possibly help me please.

I'm using the code below to allow the user to remove cell contents from one or multiple rows.

[vba]
Sub DelRow()
Dim msg

Sheets("Input").Protect "handsoff", UserInterFaceOnly:=True
Application.EnableCancelKey = xlDisabled
Application.EnableEvents = False
msg = MsgBox("Are you sure you want to delete this row?", vbYesNo)
If msg = vbNo Then Exit Sub
Selection.SpecialCells(xlCellTypeConstants).ClearContents
Selection.Interior.ColorIndex = xlNone
Application.EnableEvents = True

End Sub[/vba]
What I'm having difficulty with is changing the cell fill colour.

When the cell contents are removed I'd like:

• Columns "A:R" to have no cell fill,
• Columns "S:AD" to have the fill colour number "37" and,
• Columns "AF:AQ" to have the fill colour number "42"

I did start by trying to change this section:

[vba] Selection.SpecialCells(xlCellTypeConstants).ClearContents
Selection.Interior.ColorIndex = xlNone[/vba]
to
[vba]With ActiveCell
Range(Cells(.Row, "B"), Cells(.Row, "R")).Select
Selection.Interior.ColorIndex = xlNone
Selection.SpecialCells(xlCellTypeConstants).ClearContents
End With[/vba]

With this I could set the correct fill colours, but in the scenario of selecting multiple rows it only correctly set the cell fill colours for the first row.

I just wondered whether someone could possibly take a look at this please and let me know where I'm going wrong.

Many thanks and kind regards

Chris

[mikerickson]

Something like
[VBA]With Selection
Application.Intersect(.Parent.Range("A:R"), .EntireRow).Interior.ColorIndex = xlNone
Application.Intersect(.Parent.Range("S:AD"), .EntireRow).Interior.ColorIndex = 37
Application.Intersect(.Parent.Range("AF:AQ"), .EntireRow).Interior.ColorIndex = 42
End With[/VBA]

[hobbiton73]

Hi @mikerickson, thank you for taking the time to reply to my post.

Your solution works great, thank you so much !

All the best and a very happy New Year.

Kind Regards

Chris