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