Need custom Formula to count only bold cells in a filtered column - using a sumproduct with subtotal
My report uses TSQL to bring data from a database and then uses VBA to create a Excel report. The report has an auto filter across the columns.
The records returned are bolded for the first instance of a name, and the second instance is a light gray.
The Excel cell B4 has a formula that counts the visible rows for the text matching in A4.
Now, I need to only count the bold cells.
Shown is the working formula in the Cell. The working code that puts the formula in the cell, and a small screen shot.
My guess is that some kind of conditional format need to be embedded in the subtotal section. Any suggestions would be appreciated.
Excel cell B4 contains
=SUMPRODUCT(SUBTOTAL(3, OFFSET(C6:C591, ROW(C6:C591)-ROW(C6),0,1)),--(C6:C591=A4))
VBA code counts recordset (intMaxRecordCount) and generates dynamic formula
ObjXL.Range("B4").Select
ObjXL.Range("B4").Select ' offset 2
ObjXL.ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(SUBTOTAL(3, OFFSET(R[2]C[1]:R[" & intMaxRecordCount & "]C[1], ROW(R[2]C[1]:R[" & intMaxRecordCount & "]C[1])-ROW(R[2]C[1]),0,1)),--(R[2]C[1]:R[" & intMaxRecordCount & "]C[1]=RC[-1]))"
Bookmarks