Automatically add a row a table after filling a cell

[MasterBash]

Hi,

Lets say I have a table with 5 columns, and 20 rows. The rows are divided by fill colors, so lets say 5 rows are blue, 5 are yellow, 5 are red and the last 5 and green.

If I enter data in column B inside a cell that has a yellow background, I would like Excel to automatically add a new row below it in yellow.

How can I do that? I have multiple sheets, I would like to do it only in one sheet (lets call it Sheet1).

 

[MasterBash]

I could try using multiple tables (one for each colors), so each tables can just expand with their right background color.

However, I still don't know how to automatically add a blank row at the bottom of a table everytime I enter something in the last row.

 

[Eric W]

If you use an actual Excel table (select the table and press Control-T), then the table will automatically expand with formatting if you type data on the row below it.

 

[MasterBash]

I am trying to make it as easy as possible to use, so I am looking to automate as many things as possible.

As an example in the screenshot above, if I write something in B8, I would like the table to automatically add a blank row below it that is also red.
If I add data in B13, I would like a new empty row below B13 that will also be green. In other words, B14 becomes B15, while B14 becomes a new empty green row. I want the table to keep expanding that way.

And so on... If I use 5 tables for the 5 colors (one table each color), then when I write something in lets say B4 and I press enter, it doesn't expand the tables because all tables are too close together, so it just switches to another table. (I have to tab through the rest of the row to expand the table with a new row).

What I want is to do all that automatically. I press enter, I get a new empty row with the same background color from the row above it.

It doesn't matter if its one whole table or 5, as long as the solution works. I don't know which one is easier.

 

[MasterBash]

Anyone?

 

[Eric W]

Assuming you have a table named Table1 on Sheet1, select Sheet1. Right click on the sheet tab on the bottom and select View Code. Paste the following code in the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim T1 As Range, r1 as Range

    Set T1 = Range("Table1")
    If Intersect(Target, T1) Is Nothing Or Target.Value = "" Then Exit Sub
   
    If Target.Interior.ColorIndex <> Target.Offset(1).Interior.ColorIndex Then
        Application.EnableEvents = False
        Rows(Target.Row).Insert shift:=xlUp
        Set r1 = Cells(Target.Row, T1.Column).Resize(, T1.Columns.Count)
        r1.Offset(-1).Value = r1.Value
        r1.ClearContents
        Target.Offset(-1, 1).Select
        Application.EnableEvents = True
    End If
   
End Sub

Go back to Excel and test it out.

 

[MasterBash]

Thank you! It works well, but one little problem. On the right side of the table, I added a little text with what the colors actually mean, inside multiple cells. The problem I am having is that when I add something inside the table, it expands, but the text that is outside the table also expands, as in, it adds a new empty row in the middle of the text. Is it possible to only make the table expand? Like a new row that is only inside the table?

I apologize, I should have mentioned it in my initial post.

Thanks!

 

[Eric W]

Trickier problem. Is there anything below the table? How many rows are in it, or how many do you expect to add to it? What else is in the table? Is it all values, or are some of the columns formulas?

It also seems that if you add rows to the table, but not to the text outside the table, that text and the text within the table will gradually get separated. Can you just make sure that any text outside the table must be above the last row of each color?

 

[MasterBash]

There is nothing below the table but it is an always expanding table, so at one point it will be pretty big. This table is simply a reference for formulas on other sheets. Table columns are from A to L, they are static. Only new rows get added.

Column cells are different size too. Best is to have it on the right side of the table, but if that is too much work, I will see if there are any other solutions.