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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=375581

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 <atyahoo<dotcom
----
"mangesh_yadav"
wrote in message
news:mangesh_yadav.1py5ae_1117627508.3751@excelfor um-nospam.com...

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

Set the TakeFocusOnClick property if the command button to false.

--
Regards,
Tom Ogilvy

"Max" wrote in message
...
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 <atyahoo<dotcom
----
"mangesh_yadav"

wrote in message
news:mangesh_yadav.1py5ae_1117627508.3751@excelfor um-nospam.com...

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" 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 <atyahoo<dotcom
----

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" wrote in message
...
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 <atyahoo<dotcom
----
"mangesh_yadav"

wrote in message
news:mangesh_yadav.1py5ae_1117627508.3751@excelfor um-nospam.com...

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 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 <atyahoo<dotcom
----

Glad that it worked. Thanks for the feedback.

Mangesh



"Max" wrote in message
...
"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 <atyahoo<dotcom
----