## Counting conditionally formatted cells (vba)

**UPDATE**: **It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you tweak the conditional formatting formula to count conditionally formatted cells.**

The user defined function in cell D2 counts cells with the same cell color as cell C2. Conditional formatting was applied to cell range A2:A20, formula:

=A2

There are 10 red cells in cell range A2:A20 and the user defined function returns 10 i cell D2. This udf can only compare and count cell colors and conditional formatting formulas.

### VBA code

Function CountCFCells(rng As Range, C As Range) Dim i As Single, j As Long, k As Long Dim chk As Boolean, Str1 As String, CFCELL As Range chk = False For i = 1 To rng.FormatConditions.Count If rng.FormatConditions(i).Interior.ColorIndex = C.Interior.ColorIndex Then chk = True Exit For End If Next i j = 0 k = 0 If chk = True Then For Each CFCELL In rng Str1 = CFCELL.FormatConditions(i).Formula1 Str1 = Application.ConvertFormula(Str1, xlA1, xlR1C1) Str1 = Application.ConvertFormula(Str1, xlR1C1, xlA1, , ActiveCell.Resize(rng.Rows.Count, rng.Columns.Count).Cells(k + 1)) If Evaluate(Str1) = True Then j = j + 1 k = k + 1 Next CFCELL Else CountCFCells = "Color not found" Exit Function End If CountCFCells = j End Function

The code is tested in excel version 2010.

### Where do I put the code?

- Open the VB Editor or press short cut key Alt+F11.
- Click "Insert" on the menu
- Click "Module"
- Paste code to module
- Exit VB Editor
- Save the workbook as an *.xlsm file

### Download *.xlsm file

### Category: Conditional formatting

Highlight dates within a date range using conditional formatting

Question: How do I highlight dates that meet a criteria using conditional formatting? Table of contents Highlight values in a […]Comments(15) Filed in category: Conditional formatting, Excel

Highlight closest value in excel

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]Comments(10) Filed in category: Conditional formatting, Excel

### Category: Count values

Count unique distinct values that meet multiple criteria in excel

This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria. Tip! […]Comments(93) Filed in category: Count values, Excel, Unique distinct values

Count unique distinct values in a column in excel

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: Table of Contents Count […]Comments(26) Filed in category: Count values, Excel, Unique distinct values, Unique values

### Category: User defined functions udf

Extract unique distinct values from a filtered table (udf and array formula)

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Comments(17) Filed in category: Excel, Sort values, Unique distinct values, User defined functions (udf), VBA

Comments(16) Filed in category: Count values, Excel, Frequency, User defined functions (udf), VBA

### Category: Vba

Comments(40) Filed in category: Excel, VBA

Copy selected rows (checkboxes) (2/2)

This post describes how to copy selected rows using checkboxes. In a previous post I showed you how to add/remove […]Comments(30) Filed in category: Check-boxes, Excel, VBA

### 26 Responses to “Counting conditionally formatted cells (vba)”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hi,

Really brilliant UDF fella! one thing tho...

I have used it for a spreadsheet of mine and have coune accross a problem. When using the code to count the colour red its is also counting cells that are blank (colour wise) as red? I have got around it using another formular but wondered if there was anything you cound do?

thanks

James,

I don´understand, can you upload an example file?

This is a really awesome tutorial and MUCH needed function!

I wonder if there is a way for it to work in Excel 2013? I tried it with a basic color and with Excel's Conditional Formatting, and no luck with either, unfortunately.

Andrew,

There is something wrong with the 2010 version also.

Oh! So, does that mean you are working on it, or have you abandoned this function? It would be really awesome and worth pursuing. So many people could use this.

Anyway the tutorial is well done, so if the VBA code gets fixed, please do post an update! Should get many website hits for you because its such a useful analysis.

Thanks.

I am trying to do this with multiple columns.

Example:

I have an employee schedule that will highlight a color per half hour if it is between the employee schedule.

Rows: Employee's

Columns: Time by half hour

I want to count how many employees are working during each half hour by counting the highlighted cell. This formula works for one cell but then i get errors with others. it says "Formula emits adjacent cells"

Any idea what that means?

I try it same, but it got output as 0.

And i check with different cells.

Can any one help me.

Note: If i select empty cell i get color not found.

Same here, I got "color not found even after changing the color to match the picture.

I have found that the script only works for one conditional formatting rule at a time. If you select a group of cells that have different rules, the script only gives the result of the first rule. Does anyone know how to make it work with more than one rule?

Hi

This works great but for two cells or more, but doesn't work for a single cell. It always says 0 regardless. Is there an easy change to make this work for one cell i.e. does this cell have the conditional formatting active?

Brett,

The macro is not working as I thought it would and I don´t know how to fix it.

good day

I added some more numbers in the column and colored another several cells ,

on d2 I changed =countcfcells(a2:a17,c2) to =countcfcells(a2:a24,c2)

then I received #value!

only the first quantity works.

a2:a17

and not a2:a24

I try to understand.

for several hours -no success.

can you help ?

thanks

yosef

This is much more succinct than Chip Pearson's monster code. However, it does not work for Excel 2013. Even downloading your workbook, as soon as it's downloaded the 10 changes to #VALUE!

Furthermore it is unclear how you colored the blank cell - did you simply fill with a background of red? Don't think so but you did not explain. Sorry we aren't mind readers - smile. If you did not, how did you conditionally format that blank cell to be red? I tried various ways and none worked. This is frustrating as hell not to be able to simply count the colored cells from CF.

Thanks for any assistance,

Mort in Dallas

Hi mate, I am trying to do the Conditional format colour count and I have pasted and tried to use the function. But when I run the function it says that the colour was not found. Can you please help me with that.

okey i had used this code it gives me a error

Compile Erorr :

Syntax error:

(Module 1 5:0)

Function CountCFCells(rng As Range, C As Range)

Dim i As Single, j As Long, k As Long

Dim chk As Boolean, Str1 As String, CFCELL As Range

chk = False

For i = 1 To rng.FormatConditions.Count

If rng.FormatConditions(i).Interior.ColorIndex = C.Interior.ColorIndex Then

chk = True

Exit For

End If

Next i

j = 0

k = 0

If chk = True Then

For Each CFCELL In rng

Str1 = CFCELL.FormatConditions(i).Formula1

Str1 = Application.ConvertFormula(Str1, xlA1, xlR1C1)

Str1 = Application.ConvertFormula(Str1, xlR1C1, xlA1, , ActiveCell.Resize(rng.Rows.Count, rng.Columns.Count).Cells(k + 1))

If Evaluate(Str1) = True Then j = j + 1

k = k + 1

Next CFCELL

Else

CountCFCells = "Color not found"

Exit Function

End If

CountCFCells = j

End Function

The format can count only greater than or less than conditional format only. can't count between condition format.

EX.

Red colour ratio = 1 to 7

Yellow colour ratio = 8 to 11

Green colour ratio = 12 to 15

Blue colour ratio = above 15

Do you know of anyway to count up only the visable rows? I have some filtered data and I need to just get the visable rows, do a count of each color, and get the average.

Hello, the formula works perfectly if conditional formatting is "cell is greater than".

But when you use a formula like this in conditional formatting: "=if(A1=1,true,false) the formula countofcells returns "#VALUE!"

Any ideas how to make it work with more complex conditional formatings ???

Hi,

This works perfectly in columns, but what part do I have to change in VBA to count in rows?

Thanks in advance for your advice.

I have the same question as Steven, the columns work great, but I can't get it to work on rows. What do I need to do.

Thanks

Tommy and Steven,

Don't use the macro in this post, it is not working.

I recommend you tweak and use your conditional formatting formula to count conditionally formatted cells.

Oscar

Thanks for the quick reply, but when I do the same thing you sent me it doesn't work. How can I send you my file so you can see what i'm trying to do. I don't understand how to tweak my conditional formatting formula to count the cells.

Thanks again

Oscar, your code is currently working when I download it and I think it will continue to work on the file that I need it to. However, I need to adjust the code to count rows not collumns, what portion would I modify in order to do so?

Thanks in Advance

Shan

The code is not reliable, I recommend you don't use it.

Hi Oscar,

I tried to change the conditional formatting ie:format only cells that contain(cell value = 0) and the result is zero though the red cells are 6.

Please help.

Thanks you

rechel

Yes, the macro is not working. Do not use it.