Add Styles with VBA

Create a palette sheet and format cells as you like. Leave a blank row between them to have independent borders.

Add the code to “read” the palette and create the styles.

 
 
  1. Option Explicit
  2. Sub CreateStyles()
  3.     Dim lr
  4.     Dim i
  5.     Dim StyleName, StyleCell As Range
  6.    
  7.     lr = Cells(Rows.Count, 2).End(xlUp).Row
  8.    
  9.     For i = 3 To lr Step 2
  10.         Set StyleCell = Cells(i, 2)
  11.         StyleName = Cells(i, 3).Value
  12.        
  13.         ' first delete
  14.        On Error Resume Next
  15.         ActiveWorkbook.Styles(StyleName).Delete
  16.         On Error GoTo 0
  17.         ' add style
  18.        ActiveWorkbook.Styles.Add Name:=StyleName
  19.        
  20.         With ActiveWorkbook.Styles(StyleName)
  21.             .Borders(xlBottom).Color = StyleCell.Borders(xlBottom).Color
  22.             .Borders(xlLeft).Color = StyleCell.Borders(xlLeft).Color
  23.             .Borders(xlRight).Color = StyleCell.Borders(xlRight).Color
  24.             .Borders(xlTop).Color = StyleCell.Borders(xlTop).Color
  25.             .Interior.Color = StyleCell.Interior.Color
  26.             .Font.Color = StyleCell.Font.Color
  27.             .NumberFormat = StyleCell.NumberFormat
  28.             .Locked = StyleCell.Locked
  29.             .FormulaHidden = StyleCell.FormulaHidden
  30.         End With
  31.     Next i
  32. End Sub