View Single Post
 
Old 12-21-2016, 11:20 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

The little black marker in the lower right corner of cell H whatever row that is indicates column I is not part of the table.

Try this
Code:
Sub CloudForgiven()

    Dim colIndex As Variant
    Dim StrtRow As Long, EndRow As Long, i As Long
    Dim oLo As ListObject
    
    colIndex = Application.InputBox("Enter a column that you want to add: ", "What column?")
    If colIndex = "" Then Exit Sub
    With ThisWorkbook.Sheets("Sheet1")
        .Columns(colIndex).Insert shift:=xlRight  '<--| reference column you want to insert
        
        'sheet row numbers from table rows
        Set oLo = .ListObjects(1)    '<~~ first table on sheet
        With oLo
            StrtRow = .ListRows(1).Range.Row
            EndRow = .ListRows.Count + StrtRow - 1
        End With
    
        For i = StrtRow To EndRow
            .Cells(i, colIndex).Interior.Color = .Cells(i, 1).DisplayFormat.Interior.Color
        Next i
    End With

End Sub
Reply With Quote