PDA

View Full Version : Solved: Change Cell Fill Colour



hobbiton73
12-30-2012, 08:36 AM
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.


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
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:

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

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
12-30-2012, 08:40 AM
Something like
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

hobbiton73
12-30-2012, 09:00 AM
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