use of a variable......

[ltyson]

I use excel 2003 and have a sheet that has 258 small rectagular boxes on
it.

I use the fill color option to turn the boxes black if I want to show
that I selected it and make it stand out.

I was trying to create a macro that would take all 258 rectagle boxes
and change their fill color to "no fill" to sort of 'clear' the sheet
when needed. Problem is if I record the macro and start selecting all
258 then select the no fill option, the macro errors out on playback
because it says it is to large.

The boxes are named Rectangle 232, Rectangle 233, Rectangle 234, etc
from 1 to 258. So I thought about using some sort of FOR X = 1 to 258
type routine that would clear out the boxes and the lines of code would
then be very short but I can not figure out the correct syntex to use.

This is what the code looks like if I simply select record a macro,
then select one of the rectangles and then select 'no fill' and end the
macro:

Sub sample_clear()
'
ActiveSheet.Shapes("Rectangle 232").Select
Selection.ShapeRange.Fill.Visible = msoFalse
End Sub


I was thinking something like:

For X = 1 To 258
ActiveSheet.Shapes("Rectangle (X)").Select
Selection.ShapeRange.Fill.Visible = msoFalse
Next X
End Sub

would work, but I can not seem to get the syntex correct on how to
write in the variable.

Any help would be great if I have not confused everyone .

Thanks,
Larry

 

[Norman Jones]

Hi L,

Providing there are no other colored rectangles on the active sheet, try:

Sub Tester

ActiveSheet.Rectangles.ShapeRange.Fill.ForeColor.SchemeColor = 1

End Sub


To return the rctangles to black, change 1 to 0.

 

[Norman Jones]

Hi L,

For the looping solution, try:

Sub Tester()
Dim i As Long

For i = 1 To 258
ActiveSheet.Rectangles("Rectangle " & i). _
ShapeRange.Fill.ForeColor.SchemeColor = 1
Next i

End Sub

 

[ltyson]

Norman,
Both of your suggestions worked perfectly for what I was trying to do.

Thank you for the help,
Larry