VBA macro to add coloured and named columns between every column in sheet

[HeadAgainstABrickWall]

Hi all, crossposted on Stack overflow:

I'm doing some data cleaning and am trying to reduce the load before I fully automated the process through macros. Currently using the following to add alternating rows:

Sub AddColumns
For colx= [X] to [Y] Step 2
Columns(colx).Insert Shift:=xlToRight
Next
EndSub

What I want to add in is changing the colour of these new columns as they are added, and also automatically renaming them based on the column header before - e.g. the column added after "Type" would be "Check Type", the column added after "Name" would be "Check Name".

I'm struggling to even figure out how to start - I'm guessing that it would be on a per-column basis to colour the column named Column 1, and then to change header name to "Check"&value of c-1?

All suggestions welcome!

 

[mohadin]

Hi
What about as "how to start"

Sub AddColumns()
    For colx = 8 To 2 Step -1
        Columns(colx).Insert Shift:=xlToRight
        Cells(1, colx) = "CHECK " & Cells(1, colx - 1)
        Cells(1, colx).Interior.Color = vbRed
    Next
End Sub

 

[HeadAgainstABrickWall]

Hi
What about as "how to start"

Sub AddColumns()
    For colx = 8 To 2 Step -1
        Columns(colx).Insert Shift:=xlToRight
        Cells(1, colx) = "CHECK " & Cells(1, colx - 1)
        Cells(1, colx).Interior.Color = vbRed
    Next
End Sub

You are fanTASTIC. Thanks so much, I'm still learning VBA and am still working away from using Select.

 

[mohadin]

You are very welcome
And thank you for the feedback
Be happy and safe

 

[HeadAgainstABrickWall]

Hi
What about as "how to start"

Sub AddColumns()
    For colx = 8 To 2 Step -1
        Columns(colx).Insert Shift:=xlToRight
        Cells(1, colx) = "CHECK " & Cells(1, colx - 1)
        Cells(1, colx).Interior.Color = vbRed
    Next
End Sub

Hi Mohadin, just wondering if you could help me tweak this. i have extendend it to add two columns instead of one using Columns(colx).Resize(,2), which works a treat for the adding and colouring but I'm struggling to add the name for the second column = "Revised " & Cells(1, colx-2) just gets me "Revised " added X number of loops.

 

[mohadin]

Hi
Do you mean some thing like:

Sub AddColumns()
    For colx = 8 To 2 Step -1
        Columns(colx).Resize(, 2).Insert Shift:=xlToRight
        Cells(1, colx) = "CHECK " & Cells(1, colx - 1)
        Cells(1, colx + 1) = "REvised " & Cells(1, colx - 1)
        Cells(1, colx).Resize(, 2).Interior.Color = vbRed
    Next
End Sub

Another option same task

Sub AddColumns()
Dim colx As Long
    For colx = 8 To 2 Step -1
        Columns(colx).Resize(, 2).Insert Shift:=xlToRight
        With Cells(1, colx)
        .Value = "CHECK " & .Offset(, -1)
        .Offset(, 1) = "REvised " & .Offset(, -1)
        .Resize(, 2).Interior.Color = vbRed
        End With
    Next
End Sub

 

[HeadAgainstABrickWall]

Hi Mohadin, just wondering if you could help me tweak this. i have extendend it to add two columns instead of one using Columns(colx).Resize(,2), which works a treat for the adding and colouring but I'm struggling to add the name for the second column = "Revised " & Cells(1, colx-2) just gets me "Revised " added X number of loops.

I

Hi
Do you mean some thing like:

Sub AddColumns()
    For colx = 8 To 2 Step -1
        Columns(colx).Resize(, 2).Insert Shift:=xlToRight
        Cells(1, colx) = "CHECK " & Cells(1, colx - 1)
        Cells(1, colx + 1) = "REvised " & Cells(1, colx - 1)
        Cells(1, colx).Resize(, 2).Interior.Color = vbRed
    Next
End Sub

Another option same task

Sub AddColumns()
Dim colx As Long
    For colx = 8 To 2 Step -1
        Columns(colx).Resize(, 2).Insert Shift:=xlToRight
        With Cells(1, colx)
        .Value = "CHECK " & .Offset(, -1)
        .Offset(, 1) = "REvised " & .Offset(, -1)
        .Resize(, 2).Interior.Color = vbRed
        End With
    Next
End Sub

I figured out the first option by myself! The second is great too, very informative, I will incorporate offset into my macros!

Thanks again ?

 

[mohadin]

Glad for you
Well done