VB macro to change shape colours
 

Hi,

I have trying to do the following:

I have 180 graphic items named (shape_DI001 to shape_DI180) and 180 named
values on the spreadsheet (value_DI100 to value_DI180)

So I would like to create a macro that would:

For Counter = 1 To 180

ActiveSheet.Shapes("shape_DI"+ counter).Select

If Range("value_DI" + counter) < 10 Then
Selection.ShapeRange.Line.ForeColor.SchemeColor = 10
Else
Selection.ShapeRange.Line.ForeColor.SchemeColor = 20
End If

Next Counter

Would this be correct way of achieving this?

Thanks

VB macro to change shape colours
 

Try changing the + to & .
"shape_D1"+counter is a type mismatch but
"shape_D1" & counter automatically converts the counter to a string.

HTH

-John Coleman

Sanjay wrote:
Hi,

I have trying to do the following:

I have 180 graphic items named (shape_DI001 to shape_DI180) and 180 named
values on the spreadsheet (value_DI100 to value_DI180)

So I would like to create a macro that would:

For Counter = 1 To 180

ActiveSheet.Shapes("shape_DI"+ counter).Select

If Range("value_DI" + counter) < 10 Then
Selection.ShapeRange.Line.ForeColor.SchemeColor = 10
Else
Selection.ShapeRange.Line.ForeColor.SchemeColor = 20
End If

Next Counter

Would this be correct way of achieving this?

Thanks

VB macro to change shape colours
 

Probably want to use "shape_DI" & Format(counter,"000") to get a three digit
index with leading zeros.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"John Coleman" wrote in message
ups.com...

Try changing the + to & .
"shape_D1"+counter is a type mismatch but
"shape_D1" & counter automatically converts the counter to a string.

HTH

-John Coleman

Sanjay wrote:
Hi,

I have trying to do the following:

I have 180 graphic items named (shape_DI001 to shape_DI180) and 180 named
values on the spreadsheet (value_DI100 to value_DI180)

So I would like to create a macro that would:

For Counter = 1 To 180

ActiveSheet.Shapes("shape_DI"+ counter).Select

If Range("value_DI" + counter) < 10 Then
Selection.ShapeRange.Line.ForeColor.SchemeColor = 10
Else
Selection.ShapeRange.Line.ForeColor.SchemeColor = 20
End If

Next Counter

Would this be correct way of achieving this?

Thanks

VB macro to change shape colours
 

Good observation!

Jon Peltier wrote:
Probably want to use "shape_DI" & Format(counter,"000") to get a three digit
index with leading zeros.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"John Coleman" wrote in message
ups.com...

Try changing the + to & .
"shape_D1"+counter is a type mismatch but
"shape_D1" & counter automatically converts the counter to a string.

HTH

-John Coleman

Sanjay wrote:
Hi,

I have trying to do the following:

I have 180 graphic items named (shape_DI001 to shape_DI180) and 180 named
values on the spreadsheet (value_DI100 to value_DI180)

So I would like to create a macro that would:

For Counter = 1 To 180

ActiveSheet.Shapes("shape_DI"+ counter).Select

If Range("value_DI" + counter) < 10 Then
Selection.ShapeRange.Line.ForeColor.SchemeColor = 10
Else
Selection.ShapeRange.Line.ForeColor.SchemeColor = 20
End If

Next Counter

Would this be correct way of achieving this?

Thanks