PDA

View Full Version : CellColor Macro Not Working



wz72n01
07-17-2007, 07:41 AM
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

:dunno

OdiN
07-17-2007, 07:50 AM
What are you trying to do with this function?

unmarkedhelicopter
07-17-2007, 08:01 AM
Functions are not allowed to change anything, the only thing they can do is return a value to the calling cell

wz72n01
07-17-2007, 08:30 AM
I am trying to create a function that will give me a numerical value based on the color of a cell

david000
07-17-2007, 08:36 AM
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

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

xld
07-17-2007, 09:05 AM
It does what you describe, so what is the problem?

wz72n01
07-17-2007, 02:00 PM
when I load and use the function in my company's Excel book, I get an error in the cell.

mdmackillop
07-17-2007, 03:16 PM
Can you post a book to demonstrate? Use Manage attachments in the Go Advanced section.

xld
07-17-2007, 03:56 PM
If you get a #NAME error, have you loaded the functions in a standard code module.

rory
07-18-2007, 06:33 AM
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
07-18-2007, 07:47 AM
Please let me know what I am doing wrong. The security settings are on low and the script is in the correct sheet.

:banghead:

rory
07-18-2007, 07:57 AM
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
07-18-2007, 08:05 AM
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
07-18-2007, 08:10 AM
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!