Add/Remove rows with formatting Macro

[BluOx]

Hello,

I've been trying to piece a macro together from a couple different sites but haven't been able to find a solution for what I'm looking to do. I have an excel doc to track paint emissions per week as well as keep track yearly and monthly totals. I would like to add rows across all 60 some sheets in the spot above the button location while copying all the formatting in the cell above the new row. Additionally I would also like to add a button that will prompt the user for which row they would like to delete and remove it from all sheets.

I certainly appreciate any help, hopefully this will be the last step and I can be done with this document for a while!

~BluOx

 

[BluOx]

That could work. Instead of having a button for each section I would just have an add and delete button. Can we make it so it copies the formula's from the above row? So if a user were to add a number in one of the tables it would ignore it, but if there was a formula it would extend it to the new row?

 

[mrmmickle1]

BlueOx,

I believe this procedure should do the trick. Please let me know if you need further adjustments:

[COLOR=#0000ff]Sub [/COLOR]AddTableRow()

[COLOR=#0000ff]Dim[/COLOR] RowNumber [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]Dim [/COLOR]RowColor  [COLOR=#0000ff]As Integer[/COLOR]

Rownum = Application.InputBox(Prompt:="Select The Row Number to Format", Title:="Format Tables", Type:=1)
RowColor = Application.InputBox(Prompt:="Type 1 for Blue Or 2 For White", Title:="Format Tables", Type:=1)

Application.ScreenUpdating = [COLOR=#0000ff]False[/COLOR]

 [COLOR=#0000ff]For Each [/COLOR]wksht [COLOR=#0000ff]In[/COLOR] ActiveWorkbook.Worksheets
        [COLOR=#0000ff]If [/COLOR]wksht.Name <> "Master" [COLOR=#0000ff]Then[/COLOR]
           wksht.Activat
            [COLOR=#0000ff]With [/COLOR]wksht
               Rows(Rownum).Select
               Selection.Insert Shift:=xlUp
            
               [COLOR=#0000ff]  For[/COLOR] i = 3 [COLOR=#0000ff]To[/COLOR] 9 [COLOR=#0000ff]Step[/COLOR] 1
                   [COLOR=#0000ff]  If [/COLOR]Cells(Rownum - 1, i).HasFormula [COLOR=#0000ff]Then[/COLOR]
                         Cells(Rownum - 1, i).Copy Destination:=Cells(Rownum, i)
[COLOR=#0000ff]                     End If[/COLOR]
                [COLOR=#0000ff] Next[/COLOR] i
                 
                 [COLOR=#0000ff]If [/COLOR]RowColor = 1 [COLOR=#0000ff]Then[/COLOR]
                         .Range("C" & Rownum & ":I" & Rownum).Select
                         Selection.Interior.ColorIndex = 20[COLOR=#008000] 'Change Color As Needed[/COLOR]
                         Selection.Borders(xlEdgeRight).Color = -4165632
                         Selection.Borders(xlEdgeLeft).Color = -4165632
                         Selection.Borders(xlEdgeTop).Color = -4165632
                         Selection.Borders(xlEdgeBottom).Color = -4165632
[COLOR=#0000ff]                 Else[/COLOR]
                         .Range("C" & Rownum & ":I" & Rownum).Select
                         Selection.Interior.ColorIndex = 2 [COLOR=#008000] 'Change Color As Needed[/COLOR]
                         Selection.Borders(xlEdgeRight).Color = -4165632
                         Selection.Borders(xlEdgeLeft).Color = -4165632
                         Selection.Borders(xlEdgeTop).Color = -4165632
                         Selection.Borders(xlEdgeBottom).Color = -4165632
[COLOR=#0000ff]                 End If[/COLOR]
[COLOR=#0000ff]             End With[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
   [COLOR=#0000ff] Next [/COLOR]wksht

Application.ScreenUpdating = T[COLOR=#0000ff]rue[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

 

[mrmmickle1]

BluOx,

The above code contains a typo. Make sure to change this line:

[COLOR=#0000ff]For Each [/COLOR]wksht In ActiveWorkbook.Worksheets
       [COLOR=#0000ff] If[/COLOR] wksht.Name <> "Master" [COLOR=#0000ff]Then[/COLOR]
[COLOR=#ff0000][B]           wksht.Activat[/B][/COLOR]
           [COLOR=#0000ff] With [/COLOR]wksht

To this or it will not run:

[COLOR=#0000ff]For Each [/COLOR][COLOR=#000000]wksht[/COLOR][COLOR=#0000ff] In[/COLOR] ActiveWorkbook.Worksheets
      [COLOR=#0000ff]  If [/COLOR]wksht.Name <> "Master" [COLOR=#0000ff]Then[/COLOR]
          [COLOR=#ff8c00][B] wksht.Activate[/B][/COLOR]
[COLOR=#0000ff]            With wksht[/COLOR]

 

[BluOx]

You, my friend, are a genius!! The only tweak would be it's not bringing down the formula for Columns A, B, J, K & L.

Otherwise once I either match the color or figure out another color I think we'll finally be all set!

 

[mrmmickle1]

If you want to change the formulas that are copied adjust this portion of code:

                 [COLOR=#0000ff]For[/COLOR] i = 3 [COLOR=#0000ff]To [/COLOR]9 [COLOR=#0000ff]Step[/COLOR] 1  [COLOR=#008000]'3 is for Column C, 9 Is for Column I[/COLOR]
                     [COLOR=#0000ff]If[/COLOR] Cells(Rownum - 1, i).HasFormula [COLOR=#0000ff]Then[/COLOR]
                         Cells(Rownum - 1, i).Copy Destination:=Cells(Rownum, i)
[COLOR=#0000ff]                     End If[/COLOR]
                [COLOR=#0000ff] Next[/COLOR] i

So if you want to include formulas from A,B,J,K, & L then you would need to change the references to:

  [COLOR=#0000FF]For[/COLOR] i = 1 [COLOR=#0000FF]To [/COLOR][COLOR=#000000]12[/COLOR] [COLOR=#0000FF]Step[/COLOR] 1  [COLOR=#008000]'1 is for Column A, 12 Is for Column L[/COLOR]

This modification should now copy the formulas down in the columns you specified.


If you want to change the Columns that are colored then change the two occurrences of this line that read:

 .Range("C" & Rownum & ":I" & Rownum).Select

To:

 .Range("[COLOR=#ff0000]Your Start Column Lette[/COLOR][COLOR=#ff0000]r[/COLOR]" & Rownum & ":[COLOR=#ff0000]Your End Column Letter[/COLOR]" & Rownum).Select

Hope this helps get everything straightened out! All those tables were a mess

 

[BluOx]

This whole thing is a mess! It all started with a question and devolved into this as I was told more and more information that needed to be tracked...

It all works great, can't believe I couldn't figure out it was just numbering the columns, but it does exactly what I'm wanting it to do! I just need to play with the colors and figure out how to force it to show grid lines (some do some don't for some reason).

Thank you so very very much for all your help. Now I should be able to get out from under this monstrosity of a spreadsheet and turn it over to the people who actually do the tracking!

Mrmmickle1, you are a God among men (or ladies whichever is the case)! ray:

If you ever need someone to come up with a messy spreadsheet I'm your man!

 

[mrmmickle1]

Just change these lines of code:

[COLOR=#0000ff] If[/COLOR] RowColor = 1 T[COLOR=#0000ff]hen[/COLOR]
                         .Range("C" & Rownum & ":I" & Rownum).Select
                         Selection.Interior.ColorIndex = 20
                         Selection.Borders(xlEdgeRight).Color = -4165632
                         Selection.Borders(xlEdgeLeft).Color = -4165632
                         Selection.Borders(xlEdgeTop).Color = -4165632
                         Selection.Borders(xlEdgeBottom).Color = -4165632
[COLOR=#0000ff]                 Else[/COLOR]
                         .Range("C" & Rownum & ":I" & Rownum).Select
                         Selection.Interior.ColorIndex = 2
                         Selection.Borders(xlEdgeRight).Color = -4165632
                         Selection.Borders(xlEdgeLeft).Color = -4165632
                         Selection.Borders(xlEdgeTop).Color = -4165632
                         Selection.Borders(xlEdgeBottom).Color = -4165632

To Reflect This:

[COLOR=#0000ff]  If[/COLOR] RowColor = 1 [COLOR=#0000ff]Then[/COLOR]
                         .Range("C" & Rownum & ":I" & Rownum).Select
                         Selection.Interior.ColorIndex = 20 'Change Color As Needed
                         Selection.Borders(xlEdgeRight).Color = -4165632
                         Selection.Borders(xlEdgeLeft).Color = -4165632
                         Selection.Borders(xlEdgeTop).Color = -4165632
                         Selection.Borders(xlEdgeBottom).Color = -4165632                         
[COLOR=#ff0000][B]                         Selection.Borders(xlInsideHorizontal).Color = -4165632[/B][/COLOR]
[COLOR=#ff0000][B]                         Selection.Borders(xlInsideVertical).Color = -4165632[/B][/COLOR]
[COLOR=#0000ff]                 Else[/COLOR]
                         .Range("C" & Rownum & ":I" & Rownum).Select
                         Selection.Interior.ColorIndex = 2  'Change Color As Needed
                         Selection.Borders(xlEdgeRight).Color = -4165632
                         Selection.Borders(xlEdgeLeft).Color = -4165632
                         Selection.Borders(xlEdgeTop).Color = -4165632
                         Selection.Borders(xlEdgeBottom).Color = -4165632
[B][COLOR=#ff0000]                         Selection.Borders(xlInsideHorizontal).Color = -4165632[/COLOR][/B]
[B][COLOR=#ff0000]                         Selection.Borders(xlInsideVertical).Color = -4165632[/COLOR][/B]

Your most welcome.

You can use this website for color references: COLOR INDEX

-Matt

 

[BluOx]

I couldn't match the color that I wanted with the color index so I changed it from index to the RGB code

Selection.Interior.Color = RGB(219, 229, 241)

Out of curiosity, what does the -4165632 do? Is that the color of the grid?

 

[mrmmickle1]

Yes, That's Right that specifies the grid color.

If you want to test out some colors or get a specific color you can color a cell and then "Query It" by using the Immediate Window (In the VBE use Ctrl + G).

For Example:

1. Color Cell A1.
2. Select Cell A1
3 Access the VBE (Alt + F11)
4. Access the Immediate Window (Ctrl + G)
5. Type in something like this then press ENTER:

?ActiveCell.Interior.ColorIndex

The number returned is the ColorIndex.

 

[BluOx]

I've run into a slight hiccup. The rows are inserted correctly, colors are correct, but Columns A, B and K aren't copying the formula's correctly. For instance if I try to add row 23 it inserts correctly, but the formula points to cell 24 on the master sheet instead of 23. If I do another row then it's 25 ect. But i can't seem to figure out why this is? All the other column formula's are correct....