Make sure you put this in a standard Module (not a sheet o
ThisWorksheet).

Code
-------------------
Public Function countnonwhite(rInput As Range) as Single

Dim nonwhite As Single
nonwhite = 0
countnonwhite = 0

For Each c In ActiveSheet.Range(rInput)
If c.Interior.ColorIndex < -4142 Then nonwhite = nonwhite + 1
Next

countnonwhite = nonwhite

End Su
-------------------

Use it by entering =countnonwhite(A1:C100)



--
Message posted from http://www.ExcelForum.com

Hi there, and thanks for your help

I've got an error whereby it gives me a #value error in excel.

When i paste it into a sub and get it to display the results with a
msgbox it works perfectly.

Any ideas how i can get rid of the #value errors?


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Oops...

Public Function countnonwhite(rInput As Range) As Single

Dim c As Range
Dim nonwhite As Single

nonwhite = 0
countnonwhite = 0
Debug.Print rInput.Columns.Count

For Each c In rInput
If c.Interior.ColorIndex < -4142 Then nonwhite = nonwhite + 1
Next

countnonwhite = nonwhite

End Function

Since the rInput is already a range I shouldn't have used th
Range(rInput) statement.



--
Message posted from http://www.ExcelForum.com

Thanks for the help but 1 last question.

any idea how i get this to automatically update.

At the minute the result is static (i.e. it shows the result of the
formula the last time the formula was entered into the cell.

How do we get it to be like a '=sum' whereby if the range it evaluates
changes, so does the answer.

Thanks again for all your help,
Daren.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

It should evaluate everytime the worksheet is calculated (including whe
enter a new value in a cell). If you want it to update anytime yo
change the color of a cell, I'm afraid you may be out of luck sinc
there is no event triggered when this happens.

You can add it to the Worksheet_SelectionChange event so that it wil
evaluate anytime a new cell is clicked. I.e.:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

If you are making the changes to the color via code, you can use th
Application.Calculate at the end of your code that makes the change
and get it to update.



--
Message posted from http://www.ExcelForum.com