Forum Discussion

Excel's avatar
Excel
Iron Contributor
Jun 18, 2022
Solved

Format as table with the help of VBA code

Hello Everyone, 

I have tried to change FORMAT AS TABLE in all sheets in same time with the help of VBA code.

It is run right but it came different color..

like  - 

 

 

So, what should i write in VBA code so it will come in same format?

 

Please help...

 

Here is a attached file...

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jun 18, 2022

    Excel Try this:

    Sub Macro4()
    Dim w As Worksheet
    Dim r As Range
    For Each w In Worksheets
        If w.ListObjects.Count < 1 Then
            Set r = w.Range("A1").CurrentRegion
            r.Interior.ColorIndex = xlColorIndexNone
            w.ListObjects.Add(Source:=r).Name = w.Name
        End If
    Next w
End Sub
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 18, 2022

    Excel Try this:

    Sub Macro4()
    Dim w As Worksheet
    Dim r As Range
    For Each w In Worksheets
        If w.ListObjects.Count < 1 Then
            Set r = w.Range("A1").CurrentRegion
            r.Interior.ColorIndex = xlColorIndexNone
            w.ListObjects.Add(Source:=r).Name = w.Name
        End If
    Next w
End Sub

Resources