W
wuddus
The following macro shows all the rows in a filtered list, inserts a row, and
then inserts a number in the first cell of the new row that is halfway
between the numbers over and under it:
Sub InsertRows()
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
ActiveCell.EntireRow.Insert
Cells(ActiveCell.Row, 1).Value = _
(Cells(ActiveCell.Row - 1, 1).Value + _
Cells(ActiveCell.Row + 1, 1).Value) / 2
End Sub
It works just fine. I'm trying to modify it, however, so that the new
inserted row is gray in columns A-L. I've tried the following:
Sub InsertGrayHead()
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
ActiveCell.EntireRow.Insert
With ActiveRow
Range(Cells(0, 1), Cells(0, 12)).Interior.ColorIndex = 15
Pattern = xlSolid
End With
Cells(ActiveCell.Row, 1).Value = _
(Cells(ActiveCell.Row - 1, 1).Value + _
Cells(ActiveCell.Row + 1, 1).Value) / 2
End Sub
Now I get a run-time error 424, saying that an object is required. Clearly
I've screwed up on the new range reference ("With ActiveRow..."), but I don't
know how to fix it. (I'm really new to all of this.) I'd really appreciate
any help anyone can offer!
Thanks in advance!
then inserts a number in the first cell of the new row that is halfway
between the numbers over and under it:
Sub InsertRows()
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
ActiveCell.EntireRow.Insert
Cells(ActiveCell.Row, 1).Value = _
(Cells(ActiveCell.Row - 1, 1).Value + _
Cells(ActiveCell.Row + 1, 1).Value) / 2
End Sub
It works just fine. I'm trying to modify it, however, so that the new
inserted row is gray in columns A-L. I've tried the following:
Sub InsertGrayHead()
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
ActiveCell.EntireRow.Insert
With ActiveRow
Range(Cells(0, 1), Cells(0, 12)).Interior.ColorIndex = 15
Pattern = xlSolid
End With
Cells(ActiveCell.Row, 1).Value = _
(Cells(ActiveCell.Row - 1, 1).Value + _
Cells(ActiveCell.Row + 1, 1).Value) / 2
End Sub
Now I get a run-time error 424, saying that an object is required. Clearly
I've screwed up on the new range reference ("With ActiveRow..."), but I don't
know how to fix it. (I'm really new to all of this.) I'd really appreciate
any help anyone can offer!
Thanks in advance!