VBA to format the table

[Sumanth 01]

Hi Team,

I have the need to format many different ranges of cells into similarly formatted tables, and I was hoping to write a macro to satisfy this repetitive task. The ranges already have data in them based on formulas linked to pivot tables. I tried to record a macro with the key strokes for the formatting that I require, but I was not able to completely get it right. I ran into 2 issues: (1) I couldn't figure out how to scale the macro (ie - apply the macro to the cell ranges that I didn't actually record the macro in) and (2) The macro did not record all of the formatting nuances that I require. I am a finance guy with very little understanding of coding and VBA, but this is what i would like my macro to do:

(1) Select all cells in a named range (ie - I would like to run this macro by either selecting the range myself and then using a "hot key" to perform the formatting or by selecting the top-left-most cell in the range and the macro recognizes the named range from there when I execute the "hot key" - if that is even possible)
(2) Creating the table with the range and a thick border around the range with Black Colour
(3) Fill the Header Row with a colour using RGB (i.e., Red : 169 , Green : 215 , Blue : 255).
(4) Add bold font to the headers and center headers within the cells

Any help with this would be very helpful. Thanks in advance for any responses.

 

[mse330]

Hi & welcome to the forum

You can save the below macro in a Personal Workbook, assign a shortcut key then you can run it in any file you're working on by just selecting any cell in the header row

Sub FormatRange()

With Selection.CurrentRegion
  .Borders.Weight = xlThin
  .BorderAround Weight:=xlThick
  .Rows(1).Font.Bold = True
  .Rows(1).Interior.Color = RGB(169, 215, 255)
  .Rows(1).HorizontalAlignment = xlCenter
End With

End Sub

 

[XLfriend777]

Hi Sumanth 01. Welcome to the forum. You may try the code below on a sample of your data. Remember that when you run a macro it is NOT reversible, so always first try macros on sample data. Before you run the macro below, create a range called MyRange that covers your data.

Good luck.

Sub MyTable()
'Establish a range called "MyRange" covering your data
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("MyRange"), , xlYes).Name = "NewTable"
    ActiveSheet.ListObjects("NewTable").HeaderRowRange.Select
    Selection.Interior.Color = RGB(169, 215, 255)
End Sub

 

[Sumanth 01]

Thank You Very Much ,
Mr. mse330 & Mr. goesr
It exactly matched with my need .

I am feeling accomplished by joining this forum.

 

[mse330]

Glad to help & thanks for your feedback ... Come back whenever you need help

 

[XLfriend777]

Thanks for the response. There are always people here willing to help. Especially when you are so appreciative and responsive. Don't be afraid to try new and creative things in Excel and soon you will be helping others on this forum! All the best.