COUNTING CONDITIONALLY FORMATTED COLOURED CELLS USING VBA

[J_MAW]

Hi

Hoping that you can help!

I've applied a VBA code that I got off the internet to help me count conditionally formatted coloured cells.

I only want to count "red" cells by individual row.

The VBA / Formula I am using is returning "NOCOLOR".

Could someone possibly explain where I am going wrong. Sample file can be accessed here Final Reference Tracker sample.xlsm

Thank you in advance

JMAW

 

[Dan_W]

It might be better off if you post the relevant code here if you can - quite a few people might be a little reluctant to download a workbook with VBA in it when they don't know the author, etc.

 

[J_MAW]

Thanks Dan

Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + 1
CF3 = CF3 + 1
Next CFCELL
Else
COUNTConditionColorCells = "NO-COLOR"
Exit Function
End If
COUNTConditionColorCells = CF2
End Function

I have a reference tracker and if a date "expires" the cell is conditionally formatted to red. Each colleague has a row in the sheet, and I only want to count the red cells for each individual colleague by row not the whole sheet.

I have set the VBA up then used the formula =@COUNTConditionColorCells(H2:BN2,B2) (B2 refers to a cell that has the same colour red in as the conditionally formatted cells).

I'm not experienced in VBA at all so haven't set up a macro (not experienced in these either). Is this why it's not working?

Many thanks,

JW

 

[Fluff]

Is the B2 cell manually filled or by conditional formatting?

 

[Dan_W]

Hi @Fluff - Column B is currently filled with 0, which triggers a conditional formatting rule to turn the cell red. I suspect it's just being used to try and debug the VBA code, but I might be wrong on that.

Final Reference Tracker sample.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Full Name	Color	Chasers Due	Type of Reference	Start Date	End Date	REF 01. Status	Date	Type of Reference	Contact Tel: No	Start Date	End Date	REF 02. Status	Date	Type of Reference	Contact Tel: No	StartDate	End Date	REF 03. Status	Date
2	R J		#VALUE!	British Airways	07/07/2007	29/08/2009	Requested	13-Jul-22	Cgroup		30/08/2009	01/04/2010	Requested	13-Jul-22	Airport hotel		02/04/2010	01/05/2010	Requested	13-Jul-22
3	A L		#VALUE!	British Airways	13/02/1997	31/10/2012	Received	13-Jul-22	Cgroup		22/11/2012	15/01/2014	Received	13-Jul-22	Airport hotel		02/04/2010	01/05/2010	Received	13-Jul-22
4	L T		#VALUE!	Class 1 Personnel	12/11/2007	09/05/2010	Chaser	12-Jul-22	Cgroup		10/05/2010	17/11/2011	Chaser	12-Jul-22	Airport hotel		09/01/2012	30/03/2012	Chaser	12-Jul-22
5	A W		#VALUE!	Class 1 Personnel	15/09/2009	23/06/2009	Requested	13-Jul-22	Cgroup		06/07/2010	03/02/2011	Requested	13-Jul-22	Airport hotel		10/06/2010	06/07/2010	Requested	13-Jul-22
6	C C		#VALUE!	Class 1 Personnel	06/06/2009	01/12/2009	Received	13-Jul-22	Cgroup		14/12/2009	05/04/2010	Received	13-Jul-22	Airport hotel		06/04/2010	22/08/2010	Received	13-Jul-22
7	G B		#VALUE!	Class 1 Personnel	19/11/2009	14/06/2011	Chaser	12-Jul-22	Cgroup		14/06/2011	10/11/2011	Chaser	12-Jul-22	Airport hotel		10/11/2011	15/04/2013	Chaser	12-Jul-22
8	A W		#VALUE!	Class 1 Personnel	01/01/2009	18/02/2009	Requested	13-Jul-22	Cgroup		19/02/2009	19/06/2011	Requested	13-Jul-22	Airport hotel		01/07/2011	04/12/2013	Requested	13-Jul-22
9
Reference

Cell Formulas
Range	Formula
C2:C8	C2	=COUNTConditionColorCells(H2:BN2,B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N2:N8	Expression	=IF(OR(M2="Requested",M2="Chaser"),AND(N2<TODAY()-7))	text	NO
T2:T8	Expression	=IF(OR(S2="Requested",S2="Chaser"),AND(T2<TODAY()-7))	text	NO
B2:B8	Cell Value	=0	text	NO
H2:H14	Expression	=IF(OR(G2="Requested",G2="Chaser"),AND(H2<TODAY()-7))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
S2:S9	List	Select, Requested, Received, Chaser
G2:G9	List	Select, Requested, Received, Chaser
M2:M9	List	Select, Requested, Received, Chaser

My gut instinct is that applying conditional formatting (and then counting the number of conditionally formatted cells) is not the best/most efficient method to accomplish what J MAW is trying to do, but please do let me know what you think.

 

[Fluff]

Totally agree, colour is not data & should not be used as such.

 

[Fluff]

@J_MAW rather than using a function, you can use a simple formula

=SUM(COUNTIFS(G2:BM2,{"Requested","Chaser"},H2:BN2,"<"&TODAY()-7))

 

[J_MAW]

Conditional formatting - please see below. Thank you

 

[J_MAW]

@J_MAW rather than using a function, you can use a simple formula

=SUM(COUNTIFS(G2:BM2,{"Requested","Chaser"},H2:BN2,"<"&TODAY()-7))

Sorry just seen your reply.

That works perfectly!

Thank you so much!

JMAW

 

[Fluff]

Glad we could help & thanks for the feedback.