Counting Cells with Condional Formats

Resolved · Low Priority · Version 2010

Mon 27 Nov 2017, 16:22 Delegate Jonathan said...

Jonathan has attended:
Excel VBA Introduction course
Excel VBA Intermediate course

Counting Cells with Condional Formats

I am trying to count the cells with a coloured conditional format. I tried doing the below to count the colour but it didn't work:

Function test(rRange As Range)
For Each CL In rRange
If CL.Interior.ColorIndex > 0 Then
test = test + 1
End If
Next CL
End Function

It only counted the coloured cells when they were not conditionally formatted. Any ideas? Many thanks.

Thu 30 Nov 2017, 12:05 Trainer Andreas said...

RE: Counting Cells with Condional Formats

Hi Jonathan,

Sorry for the late reply! Our VBA trainers have been incredibly busy with training this week, but one of them will be in the office tomorrow. They will be able to reply to your query then.

Regards,

Andreas
Operation Co-ordinator

Fri 1 Dec 2017, 12:35 Trainer Jens said...

RE: Counting Cells with Condional Formats

Hi Jonathan,

Thank you for the forum question.

Excel does not see conditional formatted colours as colours. You will have to replicate the conditions in your code.

Lets say the range has the conditional rule >30000, then your code should be:

Function test(rRange As Range)
For Each CL In rRange
If CL.value > 30000 Then
test = test + 1
End If
Next CL
End Function

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Create Equations in Excel 2010

Here's how to create basic mathematical
equations in your Excel 2010 worksheet.

1) On the Ribbon, click the Insert tab
2) In the Symbols group, click the arrow next to Equation
3) Select from the equations and the equation will be inserted in a text box

Or you can create your own equation:

1) Insert and select the text box
2) On the Ribbon, click the Insert tab
3) In the Symbols group, click Equation
4) The Equation Tools Design Ribbon will now be displayed

View all Excel hints and tips