Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i change the colours of my pie graph? | Charts and Charting in Excel | |||
Colours of cells change | Excel Discussion (Misc queries) | |||
Change Shape Name | Excel Programming | |||
Using If Statements to change colours | Excel Programming | |||
VBA to change chart colours | Excel Programming |