cranejc64
09-23-2010, 07:01 AM
Hello,
Rather new to VBA, using Excel 2007.. found help and was able to change a freeform shape's color to red, green, or yellow based on a cell value (excerpt of code below). Now I'd like to add a character from a cell on one sheet into the freeform shape on another sheet. So on sheet 1 I have the data, sheet 2 is a group of shapes. If cell sheet1!B2 has a "C" in it, I'd like to add that within freeform 1, in 14 pitch, bold, black font.
I run this from Sheet2 and depending on the value of Sheet1!A2 the color of freeform 1 changes. What do I need to add to get the text as above to work?
Sub ColorScribble()
ActiveSheet.Shapes("freeform 1").Select
If Range("sheet1!A2").Value = 1 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 204, 0)
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
ElseIf Range("sheet1!A2").Value = 2 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 0)
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
ElseIf Range("sheet1!A2").Value = 3 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
End If
End Sub
Much less important (let me know if I should post seperately), is there a way to color a freeform shape with the fill effect that transitions horizontally from say yellow to red, and back to yellow? I didn't see this as a fill option in excel, so may only be powerpoint, but thought maybe it could be done in VBA??
Thank you for any help you can provide.
John
Rather new to VBA, using Excel 2007.. found help and was able to change a freeform shape's color to red, green, or yellow based on a cell value (excerpt of code below). Now I'd like to add a character from a cell on one sheet into the freeform shape on another sheet. So on sheet 1 I have the data, sheet 2 is a group of shapes. If cell sheet1!B2 has a "C" in it, I'd like to add that within freeform 1, in 14 pitch, bold, black font.
I run this from Sheet2 and depending on the value of Sheet1!A2 the color of freeform 1 changes. What do I need to add to get the text as above to work?
Sub ColorScribble()
ActiveSheet.Shapes("freeform 1").Select
If Range("sheet1!A2").Value = 1 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 204, 0)
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
ElseIf Range("sheet1!A2").Value = 2 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 0)
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
ElseIf Range("sheet1!A2").Value = 3 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
End If
End Sub
Much less important (let me know if I should post seperately), is there a way to color a freeform shape with the fill effect that transitions horizontally from say yellow to red, and back to yellow? I didn't see this as a fill option in excel, so may only be powerpoint, but thought maybe it could be done in VBA??
Thank you for any help you can provide.
John