## 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.**

Recommended article:

**Working with formulas in conditional formatting**

Comments(3) Filed in category: Conditional formatting, Excel

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

How to highlight max and min value in every month in excel

Conditional formatting formula to highlight max value in every month: =B2=MAX(IF(MONTH(A2)=MONTH(Date_rng), Close, "")) Conditional formatting formula to highlight min value […]Comments(7) Filed in category: Conditional formatting, Dates, Excel

Create a dynamic border to your list using excel conditional formatting

Question: I have a list that I keep adding rows to. How do i create a border that expands as […]Comments(5) Filed in category: Conditional formatting, Excel

### Category: Count values

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. Tip! I highly […]Comments(93) Filed in category: Count unique distinct values, Count values, Excel

Count unique distinct values in a column

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

Count number of times a string exist in multiple cells using excel formula

Question: How do I find the number of occurances a word exists in a range of cells? It does not […]Comments(19) Filed in category: Count text values, Count values, Excel

Excel: List intervals between two values

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]Comments(16) Filed in category: Count values, Excel, Range

### 27 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.

Hi Oscar and Brett,

Your code is just fine. I used conditional formatting to color code a matrix (imagine a dashboard with just colors in empty cells) and used your code to count the # of a particular color in a column. It works!! yay!! Just wanted to let you know.

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.