Add 2 rows & format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nikki - 21 May 2007 02:52 GMT

Hi,

I have created a macro that adds rows with the formulas from the previous
row. This will work perfectly for people wanting to add rows within a
'section'. Now I would like to have a macro that add's a new 'section' which
is basically just a new row that is a different colour to the previous rows
and without the formulas. Can someone please help with the code to achieve
this.

More info:
The table that i have created has a blue row and then yellow rows with
details and calculations in it. There can be as many yellow rows as needed
for the detail to do with that section. To visually see a new section I have
added another blue row and then started with the yellow rows again. I have
managed to create the macro that adds the yellow rows automatically but
cannot get one that adds a blue row as it is not the same as the row before
it.

Can someone please help?

Thanks

Reply to this Message

AKphidelt - 21 May 2007 03:26 GMT

Try putting using this... I don't know the Index number for Blue off the top
of my head, so you are going to have to use a macro to find it but.

Sub bluerow()

Range("65366").End(xlUp).Offset(1,0).EntireRow.ColorIndex = "BLUE color
number"

End Sub

Once again, im not by excel so you might have to tweak it a bit, but that
basically should do it every time.

> Hi,
>
[quoted text clipped - 17 lines]
>
> Thanks

Reply to this Message

Gord Dibben - 21 May 2007 03:39 GMT

Nikki

Perhaps you can tie this into your macro somehow.

Sub Color_Blue()
Dim OldVal As Integer
Dim rng As Range
OldVal = Range("A1").Interior.ColorIndex
For Each rng In ActiveSheet.UsedRange
If rng.Interior.ColorIndex <> OldVal Then
rng.EntireRow.Interior.ColorIndex = 5
End If
Next rng
End Sub

Gord Dibben MS Excel MVP

>Hi,
>
[quoted text clipped - 17 lines]
>
>Thanks

Reply to this Message

Nikki - 23 May 2007 22:30 GMT

Hi,

How do I add the two macros below together?

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill <markhill@charm.net.noSpam>
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
Dim x As Long
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add? (Note: These rows will be added
below the one your cursor is currently on. Make sure your cursor is on a
yellow row.)", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If

'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'to handle no constants in range -- John McKee
2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
End Sub

And

Sub Color_Blue()
Dim OldVal As Integer
Dim rng As Range
OldVal = Range("A1").Interior.ColorIndex
For Each rng In ActiveSheet.UsedRange
If rng.Interior.ColorIndex <> OldVal Then
rng.EntireRow.Interior.ColorIndex = 5
End If
Next rng
End Sub

Basically what I want to do is add 2 rows and then delete the contents in
the first new row and change the colour.

> Nikki
>
[quoted text clipped - 34 lines]
> >
> >Thanks

Reply to this Message