PDA

View Full Version : Count Highlighted Cells In Each Column



barim
03-29-2018, 08:09 AM
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. 21945

mana
03-30-2018, 05:51 AM
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
04-02-2018, 01:24 PM
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
04-02-2018, 02:36 PM
VBA code work well.
You have to put the file with VBA code, in same folder with file with data