Where can I find the Index numbers for the colours?

Sub extra()
Dim N As Long
For N = 1 To 56
Cells(N , 2).Interior.ColorIndex = N
Cells(N , 2).Value = N
Next
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



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

Hi Jim
Afraid to say Im not sure what it is you have given me!!
Dont think my post explained my problem very well.
I have a UDF that Sums the contents of Comments in cells that have a certain
fill colour. I want to use the colour index number of a colour in the
function arguments eg Pale Orange=45.
Since posting I have found the colour index numbers for the 56 colours. So
far I have only got it running by ref an other cell that has the matching
fill colour in it.
My thinking is that I must be able to use, Pale Orange, colourindex45 or
something like that in the function argument. Any suggestions.

Arran

"Jim Cone" wrote:

Sub extra()
Dim N As Long
For N = 1 To 56
Cells(N , 2).Interior.ColorIndex = N
Cells(N , 2).Value = N
Next
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



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

Arran

You asked for the index numbers for the colors and Jim gave them to you.
Place the code he provided in a module in VBA and goto macro toolsmacro and
run the code and you will have (again) all the index numbers. Yes you should
have explained more in your first post as to what you needed, Jim would have
been able to help you, I am sorry to say that I can not help.

Mike Rogers

"Arran" wrote:

Hi Jim
Afraid to say Im not sure what it is you have given me!!
Dont think my post explained my problem very well.
I have a UDF that Sums the contents of Comments in cells that have a certain
fill colour. I want to use the colour index number of a colour in the
function arguments eg Pale Orange=45.
Since posting I have found the colour index numbers for the 56 colours. So
far I have only got it running by ref an other cell that has the matching
fill colour in it.
My thinking is that I must be able to use, Pale Orange, colourindex45 or
something like that in the function argument. Any suggestions.

Arran

"Jim Cone" wrote:

Sub extra()
Dim N As Long
For N = 1 To 56
Cells(N , 2).Interior.ColorIndex = N
Cells(N , 2).Value = N
Next
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



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

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?

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?

Arran,
You need to understand that I (we) do not know how the function is written
or what information is required for it to work.
You might want to ask the author of the function for help.
That of course doesn't always work.
However, I've found that reading the directions, if any, will usually solve the problem.
Sincerely,
Jim Cone


"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?

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?

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?

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?

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?