Home / Sample Macros / Format Table Style

Format Table Style

This macro is used to format a cell range as a Table with headers, borders and alternating row colors. A demo of this macro can be seen on this spreadsheet in Zoho.
Sub FormatTable()
    count = 0
    For each x in Selection.rows
        If count = 0 Then
            x.Interior.Color=RGB(255,137,0)
            x.Font.Color = RGB(255,255,255)
            With x.Borders(xlEdgeTop) 
		    .LineStyle = xlContinuous 
		    .Weight = xlThin 
		    .ColorIndex = xlAutomatic
	    End With 
	    With x.Borders(xlEdgeBottom)
	        .LineStyle = xlContinuous
		.ColorIndex = xlAutomatic
        	.Weight = xlMedium
	    End With
        Else
            If count mod 2 = 0 Then
                x.Interior.Color=RGB(255,255,200)
            End If
            With x.Borders(xlEdgeBottom)
	        .LineStyle = xlContinuous
		.Color = RGB(255,137,0)
        	.Weight = xlThin
	    End With
        End If
        count = count + 1
    Next x
    With x.Borders(xlEdgeBottom) 
	    .LineStyle = xlContinuous 
	    .Weight = xlThin 
	    .Color = black
    End With 
End Sub

    bbgulrot _  22 Aug 2017 
    Helpful, thanks!
    Vivek Sharanappa  12 May 2023 
    demo sheet not working :(
    Samuel Ponraj P  15 May 2023 
    Hello Vivek,

    Greetings from Zoho Sheet Team!

    We regret the challenge you come across.

    We suggest you use the below-mentioned macro method to run for the selection of the first 10 rows. If you still face any deviation, please share the document with us for further analysis.

    To share please follow the below-mentioned steps.
    Right-click the file and select Share to Support or Open the file, Click File-->Share--> Share to Support.
    In the comment field, please mention the ticket ID as "#88037102"
    Select the access level as Edit and Click SHARE

    Macro:

    Sub FormatTable()
    Range("A1:J10").Select
    count = 0
    For each x in Selection.rows
    If count = 0 Then
    x.Interior.Color=RGB(255,137,0)
    x.Font.Color = RGB(255,255,255)
    With x.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With x.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .Weight = xlMedium
    End With
    Else
    If count mod 2 = 0 Then
    x.Interior.Color=RGB(255,255,200)
    End If
    With x.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Color = RGB(255,137,0)
    .Weight = xlThin
    End With
    End If
    count = count + 1
    Next x
    With x.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .Color = black
    End With
    End Sub

    Hope this helps. Feel free to contact us if you require further information; we'd gladly assist you.

    Have a great day!

    Regards,
    Sam | Customer Success Team


    		 RSS of this page