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

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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.
 
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.
 
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.
 
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.
 
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.
 
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.
 
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
 
Back
Top