Good Morning,

I exported the contents of a SharePoint list to an Excel worksheet; however, I noticed the colors and formatting that have been applied to the data. This was done automatically as a part of the import process. I want to make changes to the worksheet’s formatting using a Macro without Excel severing the connection to the SharePoint list when I save it. Will that be possible? If so, can you please spell out in step by step detail what I need to do to make this happen? Thank you in advance.


1 Answer 1


The easiest way is to record a new macro and then edit the macro to be re-usable.

Here is some basic, in-ellegant code that I used for a similar need:

ActiveSheet.ListObjects(1).TableStyle = ""  'change table style
Selection.AutoFilter    'remove auto-filter
Selection.Delete Shift:=xlShiftleft    'delete first column
Selection.Delete Shift:=xlShiftUp
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.FormulaR1C1 = "Task" & Chr(10) & "Status"
    ActiveCell.FormulaR1C1 = "Task" & Chr(10) & "Description"
    Selection.ColumnWidth = 30

Cells.Select    ' Select All
With Selection        'Format Table Cells
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlTop
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .Font.Size = 9
End With
Rows("1:1").Select        'Align Headers to Bottom Center
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .Font.Bold = True
End With

ActiveWorkbook.Worksheets(1).Name = "ProjectSchedule-" & Date$      'ChangeTabName

Range("A1").Select     ' Place cursor at the top left

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.