Run VBA on multiple worksheets and sort data in table by color

I know how to record and edit to clean it up, but this action is a bit more complicated. I've pieced the code below from A LOT of different sources. When I record it, it works on the one sheet but I am unable to get it to work on both sheets without having two codes that are clunky, any guidance would be greatly appreciate. There are other things I need my VBA to do, but I should be able to figure the rest out.

The VBA needs to function on multiple worksheets (Primary and Secondary) in my workbook. The data is in a table. First I would like it to sort by color, then sort by date.

Sub SortByColor()

   Dim wks As Worksheet
   Dim tbl As ListObject
    
    Set wks = ActiveSheet
    
        For Each wks In ThisWorkbook.Worksheets
        For Each tbl In wks.ListObjects
        

        With wks.Sort
           With .SortFields
             .Clear
             .Add(tbl.Range.Columns(8), xlSortOnFontColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(84, 130, 53)
             .Add(tbl.Range.Columns(8), xlSortOnFontColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(192, 0, 0)
             .Add(tbl.Range.Columns(8), xlSortOnFontColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(198, 89, 17)
             .Add(tbl.Range.Columns(8), xlSortOnFontColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(48, 84, 150)
             .Add(tbl.Range.Columns(8), xlSortOnFontColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(38, 38, 38)

           End With
           
           .Header = xlYes
           .MatchCase = False
           .Orientation = xlTopToBottom
           .SortMethod = xlPinYin
           .Apply
        End With
        
        Next tbl
        Next wks
        
End Sub

Solution

You are pretty close, you just need to loop thru every sheet in your file, check its name and apply the formmating:

Sub SortByColorAndDate()

    Dim wks As Worksheet
    Dim tbl As ListObject
    Dim sortCol As Range
    Dim dateCol As Range

    ' Loop through only the target sheets
    For Each wks In ThisWorkbook.Worksheets
        If wks.Name = "Primary" Or wks.Name = "Secondary" Then

            For Each tbl In wks.ListObjects
                Set sortCol = tbl.ListColumns(8).DataBodyRange ' Column 8 for color
                Set dateCol = tbl.ListColumns(2).DataBodyRange ' Change to your date column index

                With wks.Sort
                    .SortFields.Clear

                    ' Sort by font color (in desired order)
                    .SortFields.Add(sortCol, xlSortOnFontColor, xlAscending).SortOnValue.Color = RGB(84, 130, 53)
                    .SortFields.Add(sortCol, xlSortOnFontColor, xlAscending).SortOnValue.Color = RGB(192, 0, 0)
                    .SortFields.Add(sortCol, xlSortOnFontColor, xlAscending).SortOnValue.Color = RGB(198, 89, 17)
                    .SortFields.Add(sortCol, xlSortOnFontColor, xlAscending).SortOnValue.Color = RGB(48, 84, 150)
                    .SortFields.Add(sortCol, xlSortOnFontColor, xlAscending).SortOnValue.Color = RGB(38, 38, 38)

                    ' Then sort by date (adjust column index if needed)
                    .SortFields.Add Key:=dateCol, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

                    ' Apply sort to the table range
                    .SetRange tbl.Range
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With

            Next tbl
        End If
    Next wks

End Sub