Is there a way to count cells in a range with a given backcolor?

[David]

Is there a function to count the cells in a range, which have a given
interior color?

 

[FSt1]

hi
Sub countcolor()
Dim rng As Range
Dim cnt As Long

Set rng = Range("A2:I30")
cnt = 0

For Each cell In rng
If cell.Interior.ColorIndex = 40 Then
cnt = cnt + 1
End If
Next
MsgBox cnt
'or Range("K2").Value = cnt

End Sub

see this site for color indexes...
http://www.mvps.org/dmcritchie/excel/colors.htm

regards
FSt1

 

[Pete_UK]

Have a look at this link:

http://www.cpearson.com/excel/SortByColor.htm

Although this is about sorting by colour, it gives you some UDFs which
will return the colour information to another column - you can then do
a COUNTIF on that column, something like:

=COUNTIF(B1:B100,4)

assuming the colour information is in column B and you are interested
in colour number 4.

Hope this helps.

Pete

 

[Mike H]

Checks a range for the same colour as in A1

Sub countcolours()
Clr = Range("A1").Interior.Color
Set Myrange = Range("B1:B100")
For Each c In Myrange
If c.Interior.Color = Clr Then
CountColour = CountColour + 1
End If
Next
MsgBox "There are " & CountColour & " cells the same colour as A1"
End Sub

Mike

 

[David]

Thanks, Mike. I knew how to do this by macro. I need a function, like
(pseudo code) "=if(interiorcolor=green, "Yes", "No").

I do recall reading an article some time ago about how this can be done.

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[David]

Thanks, FSt1. Good name!

I knew how to do this by macro. There is a way (I read an article some time
ago) of using a function, like "=if(interiorcolor=green, "Yes", "No"). It
can be done, I just can't remember how.

 

[David]

Thanks, Pete.

I read an article some time ago re: function to return interior colour of a
cell. It's not necessary to use a user-defined function. But I can't
remember how. I'm sure there are functions to return all kinds of info about
a cell's formatting.

 

[David]

Thanks, Bob.

I read an article some time ago re: function to return interior colour of a
cell. It's not necessary to use a user-defined function. But I can't
remember how. I'm sure there are built-in functions to return all kinds of
info about a cell's formatting.

 

[Mike H]

I do not believe Excel has any inbuilt functions for working with cell
colours outside of conditional formatting so I suggest it's either a Sub or a
function.

 

[David Biddulph]

The CELL function returns information about formatting in terms of number
format and whether it is coloured for negative numbers, but doesn't include
picking up basic colour formatting of cells. If you think it's not
necessary to use a user-defined function, I'm sure the readers of the group
will be interested to hear you tell them how you'll do it without.

 

[Gord Dibben]

You could try this alternate method which doesn't use VBA.

In 2003 you can specify the Format color to look for under
Edit>Find>Options>Format>Format.

Select the color from the Patterns dialog and Find All.

In the found dialog box the first cell will be highlighted. Hold SHIFT key and
scroll down to bottom of dialog box and select last cell.

The selected cells can then be counted by right-click on Status Bar and "Count".

Do for each color.


Gord Dibben MS Excel MVP