SOS Help! Code/Conditional Formatting Tables

I have a large table (Row A-S). It has a ton of different formulas and conditional formatting applied to it. I have a drop down status with a bunch of words in Column "N". Ultimately what I want to ...

excel

Macros and VBA

Kidd_Ip

MVP

May 02, 2025

How about this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Dim newRow As Range
    Dim i As Integer
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    
    ' Check if the change occurred in Column N
    If Not Intersect(Target, ws.Range("N:N")) Is Nothing Then
        If Target.Value = "Void" Or Target.Value = "Revise & Resubmit" Or Target.Value = "Rejected" Or Target.Value = "Closed" Then
            Application.EnableEvents = False
            
            ' Define the row to be copied
            Set rng = ws.Rows(Target.Row)
            
            ' Insert a new row below
            rng.Copy
            ws.Rows(Target.Row + 1).Insert Shift:=xlDown
            Set newRow = ws.Rows(Target.Row + 1)
            
            ' Convert first six columns to values
            For i = 1 To 6
                newRow.Cells(1, i).Value = newRow.Cells(1, i).Value
            Next i
            
            Application.EnableEvents = True
        End If
    End If
End Sub

Catjohnston

Copper Contributor

May 05, 2025

OMG thank you!

I am also trying to have column "E" be populated with a number (to show which revision it is on)

Is there a way when it creates the new row to be populated with a "1" and then if said row gets responded to as Revised & Resubmit and already has a number "1" then the next new row will have a "2" so on and so forth.

So basically i have my original, then if the original row has Revise & Resubmit, Void, or Rejected then this code tells it to create a new line, copy the information and formulas, and populates a "1" in column "E".

But if that new row/revision also gets marked Rejected and I want it to create another row can I tell it to recognize that column "E" already has a (1) and have it populate a (2) instead, so on and so forth.

Im not sure if that made 100% sense but any help would be greatly appreciated. Code is very new for me and I need to get this master template finished.

Also if you know of a code to be able to make all cells adjust, and keep to right font and size if something is copied and pasted into this master template that would be fantastic.

Also to have the formulas & conditional formatting i have in place be transferred down if someone pastes information that extends past the rows in the table that i have.

I Protect & Lock the sheet and I am essentially trying to make it to where there is no human error since it will be used my multiple different people and they wont have to pull the formulas down.

Forum Discussion

SOS Help! Code/Conditional Formatting Tables

Resources