Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add 2 rows & format
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add 2 rows & format
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.Col orIndex = "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. "Nikki" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add 2 rows & format
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 On Sun, 20 May 2007 18:52:00 -0700, Nikki wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add 2 rows & format
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 ' Insert Rows -- 1997/09/24 Mark Hill ' 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).SelectedShee ts 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. "Gord Dibben" wrote: 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 On Sun, 20 May 2007 18:52:00 -0700, Nikki wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format new rows. | Excel Discussion (Misc queries) | |||
Format Rows | Excel Discussion (Misc queries) | |||
format new rows | Excel Discussion (Misc queries) | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
Format rows | Excel Discussion (Misc queries) |