using vba to do a count for color cell

[Guest]

hi community

i do not really know how to use vba and this i only try myself but it did
not work..
can community please assist me more...

question:
i wanted to count cell for color and when to view some sites (was intro by
some in community) and i obtained this vba code..

Function CountYellow(MyRange As Range)
Dim iCount As Integer
Application.Volatile
iCount = 0
For Each cell In MyRange
If cell.Interior.ColorIndex = 6 Then
iCount = iCount + 1
End If
Next cell
CountYellow = iCount
End Function

and what i did was, I open my xls and activate the VB editor and at "Project
Window", I right-click the [sheet1] and choose view code,
from there, I wrote down the vba code according and
save the workbook

following the further instruction, at the worksheet, I do a fill "yellow"
into one cell, A1 thereafter
I used the code :

=CountYellow(A1:A5)

and i got a return #NAME

can someone kindly assist me in this

thanks alot to community

 

[Paul B]

Put the code in a module,

From your workbook right-click the workbook's icon and pick View Code. This
icon is at the top-left of the spreadsheet this will open the VBA editor, in
Project Explorer click on your workbook name, if you don't see it press CTRL
+ r to open the Project Explorer, then go to insert, module, and paste the
code in the window that opens on the right hand side, press Alt and Q to
close this window and go back to your workbook
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Tyla]

For quick fix here, instead of adding the code to the "Sheet" object
within the VBA Editor, add a new module and insert your code there.
That is, within the editor, do "Insert / Module" from the menu system,
then add your CountYellow() code. It shuold then work.

HTH
/ Tyla /