Ahhhhh!!! That explains it know. On the Insert Function argumentent pop up
there are two boxes RngToCheck & ChkColor. Range referance one straight
forward, I have been banging on about putting the ColorIndex for the relevant
fill colour in ChkColor where as I should have simply put a reference to a
cell that contained the colour. Simple when you know what you are doing.
To a layman it seems a useful function as it effectively gives you a cell
within a cell all be it limited to working on numbers, in its presant form.

Thanks you everybody for your help & Bob for the final explanation.

Regards Arran

"Bob Phillips" wrote:

I see. You need to pass a reference to a cell that has the colour that you
want to check for.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Arran" wrote in message
...
Hi Bob

Afraid 45 did not work either. Below is the code Nick Hodge provided

Function AddCommentsAndColours(rngToCheck As Range, chkColour As Range) As
Double
Dim mycell As Range
Dim colourNo As Integer
Dim dblFinal As Double
Application.Volatile True
colourNo = chkColour.Interior.ColorIndex
For Each mycell In rngToCheck
If mycell.Interior.ColorIndex = colourNo Then
If Not mycell.Comment Is Nothing Then
dblFinal = dblFinal + Val(mycell.Comment.Text)
End If
End If
Next mycell
AddCommentsAndColours = dblFinal
End Function

I have to hope that providing this may make what I am trying to convey
clearer. If you could possibly have one last look I would be very
grateful.

Arran

"Bob Phillips" wrote:

I think that you should just use 45.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Arran" wrote in message
...
Thank you all for your responses. Jims post did of coarse do exactly
what
I
request with your enlightenment Mike, and I found that Pale Orange's
ColorIndex = 45.
The cells I want data extracted from all have Pale Orange (ColorIndex =
45)
as the fill color.
When I call the UDF using the Insert function button the Function
Argument
window comes up wanting RngToCheck which I set to the required. It also
wants
ChkColor, and its here where I am getting things wrong. I have entered
ColorIndex = 45, in umpteen different formats,eg Color45,
xlColorIndex=45
etc
but all I get is #Value! returned. How should I be entering the cell
color
index I want in the ChkColor Argument box, if at all.
I hope this is clearer. Please be patient with me as this is my first
go
with VBA and all its associated features.

Arran

"Bob Phillips" wrote:

Look at Colorindex property in VBA help.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Arran" wrote in message
...
Where can I find the Index numbers for the colours?