CellColor Macro Not Working

[wz72n01]

Hello All,

I cannot figure out what I am doing wrong. Any help would be appreciated.

Function cellcolor(r As Range)
Application.Volatile
cellcolor = r.Interior.ColorIndex
End Function
Function FontColor(r As Range)
Application.Volatile
FontColor = r.Font.ColorIndex
End Function

[OdiN]

What are you trying to do with this function?

[unmarkedhelicopter]

Functions are not allowed to change anything, the only thing they can do is return a value to the calling cell

[wz72n01]

I am trying to create a function that will give me a numerical value based on the color of a cell

[david000]

[vba]
Function SumColorF(Area As Range, ci As Integer)
Dim sng As Single
Dim rng As Range

For Each rng In Area
If rng.Font.ColorIndex = ci Then
sng = sng + rng.Value
End If
Next rng
SumColorF = sng
End Function
[/vba] [vba]
Function SumColor(Area As Range, ci As Integer)
Dim sng As Single
Dim rng As Range

For Each rng In Area
If rng.Interior.ColorIndex = ci Then
sng = sng + rng.Value
End If
Next rng
SumColor = sng
End Function

[/vba]

[Bob Phillips]

It does what you describe, so what is the problem?

[wz72n01]

when I load and use the function in my company's Excel book, I get an error in the cell.

[mdmackillop]

Can you post a book to demonstrate? Use Manage attachments in the Go Advanced section.

[Bob Phillips]

If you get a #NAME error, have you loaded the functions in a standard code module.

[rory]

Did you put the code in the workbook you are using it in? If not, you will need to prefix the function with the name of the workbook where the code is - e.g.:
=Personal.xls!cellcolor(A1)
Also, do you have macros enabled?

Regards,
Rory

[wz72n01]

Please let me know what I am doing wrong. The security settings are on low and the script is in the correct sheet.

[rory]

The macros need to be in a standard module as xld said, not in the worksheet code module. You are also using the SumColor function incorrectly. See attached (if I get this right!)

[wz72n01]

Thanks Rory, Xld and the rest of you guys. I think I will pick up a VBA book to familiarize myself. Is there one that you would recommend for a beginner?

[rory]

Depends how quickly you pick stuff up really. A Dummies book might get too basic too quickly; John Walkenbach's Power Programming books are good, as are the Wrox Press "Excel 200x VBA" ones. Then once you are through those, the Professional Excel Development book by Bovey, Bullen and Green is great!