I'd like to know how we can count coloured cells?

A

Atomic

I'd like to know how we can count coloured cells?

i.e

If cells A1 C1 E1 F1 and G1 are coloured, I’d like to know how I can see the
total coloured cells in H1?

I suppose it’s a little bit more complicated than it looks. Any help would
be appreciated. Thank you.
 
D

David Biddulph

If they are coloured by conditional formatting, use those conditions to
count the cells.
If the cells are formatted separately, you can't do it with a formula; it
needs VBA.
 
M

Mike H

Hi,

Excel has no inbuilt functions for this but here's a user defined function
(UDF)

Alt + f11 to open VB editor and right click 'ThisWorkbook' and insert nodule
and paste the code below in.

Call with
=countcolour("A1:H1")

or any other range you want

Function countcolour(rng As Range) As Long
For Each c In rng
If c.Interior.ColorIndex <> xlNone Then
countcolour = countcolour + 1
End If
Next
End Function

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top