Function execution problem

[nsmythe]

I am very new to creating functions in excel, and macros for that matter, and was very intersted in creating a formula or function that counted the number of cells in a range that were of a certain color. I was able to copy and paste one of the samples I found on this website, but the problem is that the value that is returned does not get updated automatically when a cell within the range is changed to the color that is counted. How can I make the function remain active all the time, and not only when I am editing the formula and hit the enter key?

I also am wondering if there would be a way for me to have every cell I color in the future dark blue to have the font color change to yellow automatically.

Being this is my first post I have to say that this board is amazing!!

The function I am using is:

Public Function CountBlue(Inrange As Range)
CountBlue = 0
For Each cell In Inrange
If cell.Interior.ColorIndex = 11 Then
CountBlue = CountBlue + 1
End If
Next
End Function

Thanks

[Tom Schreiner]

How are you passing the range Inrange?
In what way?
Tom

[NateO]

This is a volatile (technically speaking) function. Change your code to:

Public Function CountBlue(Inrange As Range)
application.volatile
CountBlue = 0 
For Each cell In Inrange 
If cell.Interior.ColorIndex = 11 Then 
CountBlue = CountBlue + 1 
End If 
Next 
End Function

This will allow you to recalc. "CountBlue", however, changing the color of a cell does not inspire Excel to recalculate. You'll either need to manually recalc. (Shift-f9), change a cell's value or use a worksheet_selectionchange event. Right-click on the sheet in question, click view code and paste the following:

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

This should inspire Excel. Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-01 16:17 ]

[nsmythe]

That code worked great. Thanks a lot. What exactly would a worksheet_selectionchange event do and where would I find out. Also do you have an idea for how I can have the text change color when I change the cell's color?

[NateO]

I just edited my post, look at the 2nd piece of code and the comment above the code.

[nsmythe]

TsTom

I tried your function, and it doesnt seem to change the text yellow. Do I need to do something in addition? You guys are soooooo fast.

[NateO]

Stick with the function I gave you earlier and modify your worksheet_selectionchange() event procedure to look similar to the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Calculate
For Each cell In UsedRange
If cell <> "" And cell.Interior.ColorIndex = 11 _
Then cell.Font.ColorIndex = 36
Next
End Sub

This will hurt the performance of your workbook to some extent, to enhance performance, you may want to limit the tested range (instead of 'usedrange').

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-01 17:37 ]