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

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

"David" wrote:

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

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


On Oct 17, 11:12 am, David wrote:
Is there a function to count the cells in a range, which have a given
interior color?

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" wrote:

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

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.


"Mike H" wrote:

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" wrote:

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

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" wrote in message
...
Is there a function to count the cells in a range, which have a given
interior color?

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.




"FSt1" wrote:

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

"David" wrote:

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

Thanks, Pete.

I read an article some time ago 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.



"Pete_UK" wrote:

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


On Oct 17, 11:12 am, David wrote:
Is there a function to count the cells in a range, which have a given
interior color?

Thanks, Bob.

I read an article some time ago 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.



"Bob Phillips" wrote:

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" wrote in message
...
Is there a function to count the cells in a range, which have a given
interior color?

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" wrote:

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.


"Mike H" wrote:

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" wrote:

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

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.
--
David Biddulph

"David" wrote in message
...
Thanks, Pete.

I read an article some time ago 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.

"Pete_UK" wrote:

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

On Oct 17, 11:12 am, David wrote:
Is there a function to count the cells in a range, which have a given
interior color?

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

In 2003 you can specify the Format color to look for under
EditFindOptionsFormatFormat.

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

On Wed, 17 Oct 2007 03:12:01 -0700, David
wrote:

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