Sub to list colorindexes and the corresponding fill colours

[Max]

Hi guys,

(Apologies for the earlier post w/o any subject line)

I'm looking for a sub which can list all the colorindexes down say col A,
and all the corresponding fill colors in col B ?

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[mangesh_yadav]

Private Sub CommandButton1_Click()
Set rng = Range("A1")
For i = 0 To 50
rng.Offset(i, 0) = i
rng.Offset(i, 1).Interior.ColorIndex = i
Next i
End Sub


Mangesh

[Bob Phillips]

Hi Max,

By fill colour, do you mean the RGB equivalent?

Public Sub Colours()
Dim i As Long
Dim tmp, tRed, tBlue, tGreen

For i = 1 To 56
Cells(i, "A").Value = i
Cells(i, "C").Interior.ColorIndex = i
tmp = GetRGB(ActiveWorkbook.Colors(i), tRed, tGreen, tBlue)
Cells(i, "B").Value = "RGB(" & tRed & ", " & tGreen & ", " & tBlue &
")"
Next i
Columns("B:B").AutoFit

End Sub

--
HTH

Bob Phillips

"Max" <[email protected]> wrote in message
news:[email protected]...
> Hi guys,
>
> (Apologies for the earlier post w/o any subject line)
>
> I'm looking for a sub which can list all the colorindexes down say col A,
> and all the corresponding fill colors in col B ?
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

[K Dales]

Not sure exactly what you want, but this sub will give the colorindex in
column A, the corresponding color value (numeric, the RGB value) in column B,
and will fill the cell in column C with the specified color:

Public Sub ShowFillColors()

For i = 1 To 56
Sheets("Sheet1").Range("A" & i).Value = i
Sheets("Sheet1").Range("C" & i).Interior.ColorIndex = i
Sheets("Sheet1").Range("B" & i).Value = Sheets("Sheet1").Range("C" &
i).Interior.Color
Next i

End Sub


"Max" wrote:

> Hi guys,
>
> (Apologies for the earlier post w/o any subject line)
>
> I'm looking for a sub which can list all the colorindexes down say col A,
> and all the corresponding fill colors in col B ?
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

[Max]

Thanks, Mangesh

Despite several tries at running your sub, I kept hitting a run-time error
1004
"Unable to set the colorindex property of the interior class"

Clicking debug highlighted this line:
rng.Offset(i, 1).Interior.ColorIndex = i

What can I do ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"mangesh_yadav" <[email protected]>
wrote in message
news:[email protected]...
>
> Private Sub CommandButton1_Click()
> Set rng = Range("A1")
> For i = 0 To 50
> rng.Offset(i, 0) = i
> rng.Offset(i, 1).Interior.ColorIndex = i
> Next i
> End Sub

[Max]

Thanks, Bob

Despite several tries at running your sub, I kept hitting:
Compile error: Sub or Function not defined

and "GetRGB" was highlighted in the line:
tmp = GetRGB(ActiveWorkbook.Colors(i), tRed, tGreen, tBlue)

What can I do ?

> By fill colour, do you mean the RGB equivalent?
Actually, I'm afraid I don't know <g>. Am new at this.

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Bob Phillips" <[email protected]> wrote in message
news:#[email protected]...
> Hi Max,
>

>
> Public Sub Colours()
> Dim i As Long
> Dim tmp, tRed, tBlue, tGreen
>
> For i = 1 To 56
> Cells(i, "A").Value = i
> Cells(i, "C").Interior.ColorIndex = i
> tmp = GetRGB(ActiveWorkbook.Colors(i), tRed, tGreen, tBlue)
> Cells(i, "B").Value = "RGB(" & tRed & ", " & tGreen & ", " & tBlue
&
> ")"
> Next i
> Columns("B:B").AutoFit
>
> End Sub

[Max]

Thanks, K Dales !
Marvellous, spot on !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"K Dales" <[email protected]> wrote in message
news:[email protected]...
> Not sure exactly what you want, but this sub will give the colorindex in
> column A, the corresponding color value (numeric, the RGB value) in column
B,
> and will fill the cell in column C with the specified color:
>
> Public Sub ShowFillColors()
>
> For i = 1 To 56
> Sheets("Sheet1").Range("A" & i).Value = i
> Sheets("Sheet1").Range("C" & i).Interior.ColorIndex = i
> Sheets("Sheet1").Range("B" & i).Value = Sheets("Sheet1").Range("C" &
> i).Interior.Color
> Next i
>
> End Sub

[Tom Ogilvy]

Set the TakeFocusOnClick property if the command button to false.

--
Regards,
Tom Ogilvy

"Max" <[email protected]> wrote in message
news:[email protected]...
> Thanks, Mangesh
>
> Despite several tries at running your sub, I kept hitting a run-time error
> 1004
> "Unable to set the colorindex property of the interior class"
>
> Clicking debug highlighted this line:
> rng.Offset(i, 1).Interior.ColorIndex = i
>
> What can I do ?
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "mangesh_yadav"
<[email protected]>
> wrote in message
> news:[email protected]...
> >
> > Private Sub CommandButton1_Click()
> > Set rng = Range("A1")
> > For i = 0 To 50
> > rng.Offset(i, 0) = i
> > rng.Offset(i, 1).Interior.ColorIndex = i
> > Next i
> > End Sub
>
>

[Tom Ogilvy]

Bob forgot to furnish this function to go along with the code:

Public Function GetRGB(colour As Long, ByRef red, ByRef green, ByRef blue)


red = colour And &HFF
green = colour \ 256 And &HFF
blue = colour \ 256 ^ 2 And &HFF


End Function




--

Regards,

Tom Ogilvy



"Max" <[email protected]> wrote in message
news:[email protected]...
> Thanks, Bob
>
> Despite several tries at running your sub, I kept hitting:
> Compile error: Sub or Function not defined
>
> and "GetRGB" was highlighted in the line:
> tmp = GetRGB(ActiveWorkbook.Colors(i), tRed, tGreen, tBlue)
>
> What can I do ?
>
> > By fill colour, do you mean the RGB equivalent?
> Actually, I'm afraid I don't know <g>. Am new at this.
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Bob Phillips" <[email protected]> wrote in message
> news:#[email protected]...
> > Hi Max,
> >
>
> >
> > Public Sub Colours()
> > Dim i As Long
> > Dim tmp, tRed, tBlue, tGreen
> >
> > For i = 1 To 56
> > Cells(i, "A").Value = i
> > Cells(i, "C").Interior.ColorIndex = i
> > tmp = GetRGB(ActiveWorkbook.Colors(i), tRed, tGreen, tBlue)
> > Cells(i, "B").Value = "RGB(" & tRed & ", " & tGreen & ", " &
tBlue
> &
> > ")"
> > Next i
> > Columns("B:B").AutoFit
> >
> > End Sub
>
>

[Max]

"Tom Ogilvy" <[email protected]> wrote in message
> Set the TakeFocusOnClick property if the command button to false.

Thanks, Tom !
Yes, that did it ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"Tom Ogilvy" <[email protected]> wrote
> Bob forgot to furnish this function to go along with the code:
> Public Function GetRGB(colour As Long, ByRef red, ByRef green, ByRef blue)
> red = colour And &HFF
> green = colour \ 256 And &HFF
> blue = colour \ 256 ^ 2 And &HFF
> End Function

Thanks, Tom (again) !
Yes, that did it here, too !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Bob Phillips]

Sorry Max, muissed that function. Here it is

Public Function GetRGB(colour As Long, ByRef red, ByRef green, ByRef blue)

red = colour And &HFF
green = colour \ 256 And &HFF
blue = colour \ 256 ^ 2 And &HFF

End Function


--
HTH

Bob Phillips

"Max" <[email protected]> wrote in message
news:[email protected]...
> Thanks, Bob
>
> Despite several tries at running your sub, I kept hitting:
> Compile error: Sub or Function not defined
>
> and "GetRGB" was highlighted in the line:
> tmp = GetRGB(ActiveWorkbook.Colors(i), tRed, tGreen, tBlue)
>
> What can I do ?
>
> > By fill colour, do you mean the RGB equivalent?
> Actually, I'm afraid I don't know <g>. Am new at this.
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Bob Phillips" <[email protected]> wrote in message
> news:#[email protected]...
> > Hi Max,
> >
>
> >
> > Public Sub Colours()
> > Dim i As Long
> > Dim tmp, tRed, tBlue, tGreen
> >
> > For i = 1 To 56
> > Cells(i, "A").Value = i
> > Cells(i, "C").Interior.ColorIndex = i
> > tmp = GetRGB(ActiveWorkbook.Colors(i), tRed, tGreen, tBlue)
> > Cells(i, "B").Value = "RGB(" & tRed & ", " & tGreen & ", " &
tBlue
> &
> > ")"
> > Next i
> > Columns("B:B").AutoFit
> >
> > End Sub
>
>

[Max]

"Bob Phillips" <[email protected]> wrote
> Sorry Max, muissed that function. Here it is ...
It's ok, Bob. Got the missing piece from Tom just now
Thanks for the response !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Mangesh Yadav]

Hi Max,

If you run this macro through a standard module, you would get an error. Run
it through the module of the sheet in question. For standard module, you
need to reference the range with its parent sheet, and thats why you get an
error.

Mangesh



"Max" <[email protected]> wrote in message
news:[email protected]...
> Thanks, Mangesh
>
> Despite several tries at running your sub, I kept hitting a run-time error
> 1004
> "Unable to set the colorindex property of the interior class"
>
> Clicking debug highlighted this line:
> rng.Offset(i, 1).Interior.ColorIndex = i
>
> What can I do ?
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "mangesh_yadav"
<[email protected]>
> wrote in message
> news:[email protected]...
> >
> > Private Sub CommandButton1_Click()
> > Set rng = Range("A1")
> > For i = 0 To 50
> > rng.Offset(i, 0) = i
> > rng.Offset(i, 1).Interior.ColorIndex = i
> > Next i
> > End Sub
>
>

[Max]

"Mangesh Yadav" wrote:
> If you run this macro through a standard module,
> you would get an error. Run it through the module
> of the sheet in question. For standard module, you
> need to reference the range with its parent sheet,
> and thats why you get an error...

Hi, I got your sub to work on a command button? drawn on a sheet
but only with the setting done as advised by Tom earlier, i.e.:
"Set the TakeFocusOnClick property of the command button to False"
(this might be due to my xl97 version?)

I did change the line: "For i = 0 To 50" a little to: "For i = 1 To 56"
though, for consistency with the suggestions by the other responders

Thanks
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Mangesh Yadav]

Glad that it worked. Thanks for the feedback.

Mangesh



"Max" <[email protected]> wrote in message
news:[email protected]...
> "Mangesh Yadav" wrote:
> > If you run this macro through a standard module,
> > you would get an error. Run it through the module
> > of the sheet in question. For standard module, you
> > need to reference the range with its parent sheet,
> > and thats why you get an error...
>
> Hi, I got your sub to work on a command button? drawn on a sheet
> but only with the setting done as advised by Tom earlier, i.e.:
> "Set the TakeFocusOnClick property of the command button to False"
> (this might be due to my xl97 version?)
>
> I did change the line: "For i = 0 To 50" a little to: "For i = 1 To 56"
> though, for consistency with the suggestions by the other responders
>
> Thanks
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>