Count Highlighted Cells In Each Column

[barim]

Hello,

Would it be possible to accomplish this with a macro?

I have a folder of 150 files that I need to sum all highlighted cells per columns.
As you can see from attached workbook each column has various numbers of highlighted cells.
Fortunately, all column headers are same and stretch from A through N.
Number of rows is different and data could end with row 20, 150, 750 etc.

I need a macro that would open each file in the folder and look through these columns and sum these numbers like it is shown in the workbook.
Now, I need cumulative numbers for all files throughout all columns to give me a total of highlighted cells. This could be a new workbook.

I hope I gave you sufficient information. Thanks in advance. Sample.xlsx

[mana]

Option Explicit


Sub test()
    Dim ws As Worksheet
    Dim p As String, f As String
    Dim c As Range, i As Long, n As Long, k As Long
    
    p = "C:\*******\****\"
    
    Application.ScreenUpdating = False
    
    Set ws = ActiveSheet
    ws.UsedRange.ClearContents
    
    f = Dir(p & "*.xlsx")
    
    Do While f <> ""
        With Workbooks.Open(p & f).Sheets(1)
            For Each c In .Cells(1).CurrentRegion.Columns
                n = 0
                For i = 2 To c.Cells.Count
                    If c.Cells(i).Interior.ColorIndex <> xlNone Then n = n + 1
                Next
                c.Cells(i + 1).Value = n
            Next
            k = k + 1
            ws.Cells(k, 1).Value = f
            ws.Cells(k, 2).Resize(, 14).Value = .Cells(i + 1, 1).Resize(, 14).Value
            .Parent.Close True
        End With
        f = Dir()
    Loop
    
End Sub

マナ

[barim]

Mana, thank you so much for your reply. For some reason it doesn't work. I set up right path to the folder, but nothing happens. I do not get any error messages.

[Tom Jones]

VBA code work well.
You have to put the file with VBA code, in same folder with file with data