Button does not generate proper colors?

cloudforgiven · #1 12-21-2016, 06:27 PM

Hi here is the code I've done so far:

Code:

Dim colIndex As Variant

    colIndex = Application.InputBox("Enter a column that you want to add: ", "What column?") 
    If colIndex = "" Then Exit Sub
    With ThisWorkbook.Sheets("Sheet1").Columns(colIndex) '<--| reference column you want to insert
        .Insert shift:=xlRight 
        .Offset(, -2).Copy '<--| copy the column two columns to the left of the referenced one 
        .Offset(, -1).PasteSpecial xlPasteFormats '<--| paste formats to the new column
        Application.CutCopyMode = False
    
    End With

There is a table in my spreadsheet that has rows with different colors(2 colors gray and white that alternates in patterns), now the code above is implemented into a button and when clicked ask the user where to add a column. The column is then generated but the colors for the rows is not there? Any ideas?

here is what it looks likes: http://imgur.com/a/m04dt

Notice the I column? All the way yo the right, with the the correct length and width but no colors.
Maybe someone can try out the code and see what it does?
The table sizes keeps changing so I don't think I can put a range in since I will have to manually change it once the table size changes?

I've tried implementing

Code:

.Offset(,-1).ColorIndex = .Offset(,-3).ColorIndex

but I am getting the same results, generated columns that have no color?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
proper	snoforlife	Excel Programming	0	01-26-2016 02:16 PM
Hyperlinks Not Linking to Proper Sections of Doc	MoiraB	Word	2	08-26-2015 07:13 PM
Proper Text Format	sufian,naeem	Excel	1	05-05-2014 05:59 AM
Unable to change font colors from theme colors	choy	Word	3	08-01-2012 09:12 PM
Proper Excel Table of Contents (ToC)	judicial85	Excel	1	10-23-2011 01:35 PM