COUNTIF to count cells with conditional formatting

[Moosles]

I have an enormous range of data (10k rows x 10k columns) with conditional formatting applied to all cells in this range. The range is something like the below, which is a concatenate of the row and column header values:

	0001	0002	0003	0004	0005	0006
0001	00010001	00010002	00010003	00010004	00010005	00010006
0002	00020001	00020002	00020003	00020004	00020005	00020006
0003	00030001	00030002	00030003	00030004	00030005	00030006
0004	00040001	00040002	00040003	00040004	00040005	00040006
0005	00050001	00050002	00050003	00050004	00050005	00050006
0006	00060001	00060002	00060003	00060004	00060005	00060006

I want to do a COUNTIF to see how many cells in each row have conditional formatting applied. The conditional formatting returns RGB(255, 204, 255) when the condition is met. The condition is a MATCH formula that looks at a separate tab to see if the concatenate value exists in that tab, so the COUNTIF would not be able to use the condition as a criteria. I was looking at using a custom formula in VBA:

Function COUNT_COLOR(RANGE As RANGE, COLOR As RANGE)
Dim COLORC As Integer
Dim COUNTT As Integer
COLORC = COLOR.Interior.ColorIndex
For Each IC In RANGE
If IC.Interior.ColorIndex = COLORC Then
COUNTT = COUNTT + 1
End If
Next IC
COUNT_COLOR = COUNTT
End Function

But this only works for cells that explicitly have the color applied, rather than ones that have conditional formatting applied. What would I need to do to make this work? Thanks!

 

[RoryA]

You would need the DisplayFormat property, but that won't work in a UDF. You could probably use SUMPRODUCT instead to count values that match the other tab rather than relying on counting the CF.

 

[Peter_SSs]

Can you show us a bit of the other tab so we can see what the data there is like and how it is laid out?

What is the actual CF formula in that top-left concatenated cell in your post above?

 

[Moosles]

Can you show us a bit of the other tab so we can see what the data there is like and how it is laid out?

What is the actual CF formula in that top-left concatenated cell in your post above?

Sure, the lookup tab is just a data dump of over 500k rows which has the concatenate value in column A and various other info in the other columns:

Concatenate	Info A	Info B
00030005	gdsfgdg	jhfjytbdf
00020001	fdgsdgsfdh	fhshrthtth
00060004	fghfghfgdh	dgdgrtyhgg
00030002	eafregfdgfg	dfgdgfghwrgwerg
00050006	retrtdh	zvgzffgwdghh
00010004	fdgjryueysgh	dgdfghrthwg
00060001	agsdgrteyujhsr	bcvbgeggaeg
00040003	gsdgrteurthr	dfgdfgwreytqt
00020002	hfgnhvnbxvg	dfgfdshreytytwreg

The Conditional Formatting formula is =MATCH(D3,Lookup!$G:$G,0).

 

[Peter_SSs]

Thanks for the extra information. I'm a little confused though. There seems to be a contradiction about the concatenated values in 'Lookup' or else I am completely mis-understanding.

the lookup tab ... has the concatenate value in column A

The Conditional Formatting formula is =MATCH(D3,Lookup!$G:$G,0).

Could this UDF work for you then, rather than trying to count the coloured cells?

Function CountConcatMatches(rVals As Range, rLookup As Range) As Long
  Dim d As Object
  Dim a As Variant
  Dim uba2 As Long, i As Long, j As Long, k As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  With rLookup.EntireColumn
    a = .Resize(.Cells(Rows.Count, 1).End(xlUp).Row).Value
  End With
  For i = 1 To UBound(a)
    d(a(i, 1)) = 1
  Next i
  a = rVals.Value
  uba2 = UBound(a, 2)
  For i = 1 To UBound(a)
    For j = 1 To uba2
      If Len(a(i, j)) > 0 Then k = k + d(a(i, j))
    Next j
  Next i
  CountConcatMatches = k
End Function

I have assumed the concatenated values are in col G per the CF formula, not col A per your written description.
(Using XL2BB for your sample data would make it simple to know exactly what columns/rows are used. You might consider it for the future.)

Moosles.xlsm
	G
1
2	00030005
3	00020001
4	00060004
5	00030002
6	00050006
7	00010004
8	00060001
9	00040003
10	00020002
11	99999999
12
Lookup

Moosles.xlsm
	C	D	E	F	G	H	I	J	K
1
2		0001	0002	0003	0004	0005	0006		Count
3	0001	00010001	00010002	00010003	00010004	00010005	00010006		9
4	0002	00020001	00020002	00020003	00020004	00020005	00020006
5	0003	00030001	00030002	00030003	00030004	00030005	00030006
6	0004	00040001	00040002	00040003	00040004	00040005	00040006
7	0005	00050001	00050002	00050003	00050004	00050005	00050006
8	0006	00060001	00060002	00060003	00060004	00060005	00060006
Sheet1

Cell Formulas
Range	Formula
K3	K3	=CountConcatMatches(D3:I8,Lookup!G:G)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:I8	Expression	=MATCH(D3,Lookup!$G:$G,0)	text	NO

 

[RoryA]

Wouldn't something like this work:

=SUMPRODUCT(--ISNUMBER(MATCH(D3:I8,Lookup!$G:$G,0)))

 

[Peter_SSs]

Wouldn't something like this work:

=SUMPRODUCT(--ISNUMBER(MATCH(D3:I8,Lookup!$G:$G,0)))

OP had 10,000 rows x 10,000 columns. With my (fairly old) machine it took nearly 20 seconds to calculate my UDF. It just fell over trying to do SUMPRODUCT that big.

 

[Moosles]

Thanks for the extra information. I'm a little confused though. There seems to be a contradiction about the concatenated values in 'Lookup' or else I am completely mis-understanding.




Could this UDF work for you then, rather than trying to count the coloured cells?

Function CountConcatMatches(rVals As Range, rLookup As Range) As Long
  Dim d As Object
  Dim a As Variant
  Dim uba2 As Long, i As Long, j As Long, k As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  With rLookup.EntireColumn
    a = .Resize(.Cells(Rows.Count, 1).End(xlUp).Row).Value
  End With
  For i = 1 To UBound(a)
    d(a(i, 1)) = 1
  Next i
  a = rVals.Value
  uba2 = UBound(a, 2)
  For i = 1 To UBound(a)
    For j = 1 To uba2
      If Len(a(i, j)) > 0 Then k = k + d(a(i, j))
    Next j
  Next i
  CountConcatMatches = k
End Function

I have assumed the concatenated values are in col G per the CF formula, not col A per your written description.
(Using XL2BB for your sample data would make it simple to know exactly what columns/rows are used. You might consider it for the future.)

Moosles.xlsm
	G
1
2	00030005
3	00020001
4	00060004
5	00030002
6	00050006
7	00010004
8	00060001
9	00040003
10	00020002
11	99999999
12
Lookup

Moosles.xlsm
	C	D	E	F	G	H	I	J	K
1
2		0001	0002	0003	0004	0005	0006		Count
3	0001	00010001	00010002	00010003	00010004	00010005	00010006		9
4	0002	00020001	00020002	00020003	00020004	00020005	00020006
5	0003	00030001	00030002	00030003	00030004	00030005	00030006
6	0004	00040001	00040002	00040003	00040004	00040005	00040006
7	0005	00050001	00050002	00050003	00050004	00050005	00050006
8	0006	00060001	00060002	00060003	00060004	00060005	00060006
Sheet1

Cell Formulas
Range	Formula
K3	K3	=CountConcatMatches(D3:I8,Lookup!G:G)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:I8	Expression	=MATCH(D3,Lookup!$G:$G,0)	text	NO

This is perfect for my needs, thanks Peter!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.