Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape colors dependent on calculations
Bernie,
I like this code you gave me. I can use this. But I still have the issue of either applying it to a shape, or getting a shape to mimic the color of the cell. "Bernie Deitrick" wrote: Papa, Reading conditional formatting can be complicated. You could try a macro like this (below), which will place rectangles over cells A1:A10 with the color based on the cell values. You could also replicate your formatting logic in those cells (suing similar formulas) or link the cells (where you want the rectangles) to the cells with the values on which the color will be based. HTH, Bernie MS Excel MVP Sub Macro2() Dim myCell As Range For Each myCell In Range("A1:A10") On Error Resume Next ActiveSheet.Shapes("Color" & myCell.Address).Delete ActiveSheet.Shapes.AddShape(msoShapeRectangle, _ myCell.Left, myCell.Top, myCell.Width, myCell.Height).Select Selection.ShapeRange.Fill.Visible = msoTrue Selection.ShapeRange.Fill.Solid If myCell.Value = 90 Then Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10 'red ElseIf myCell.Value < 60 Then Selection.ShapeRange.Fill.ForeColor.SchemeColor = 57 'green Else Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13 'yellow End If Selection.Name = "Color" & myCell.Address Next myCell End Sub "Papa Jonah" wrote in message ... I have a spreadsheet that performs calculations and has conditional formatting of cells dependent on those calculations. I am trying to make a summary-type sheet that uses shapes (rectangles) that will bring all of those condition-dependent formattings together. What management wants is a "stop-light" presentation that includes the basic red, yellow, green for the different functions. In other words, I have the calculations and conditional formatting down. Now I want to tell vba to make a rectangle the same color as the conditional formatting from calculation. Did that make sense? Thanks, Papa |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Used drawing colors in shapes....lost default colors for "Fill Col | Excel Discussion (Misc queries) | |||
my curser changed from arrow shape to a cross shape???? | New Users to Excel | |||
HOW DO I CALCULATE SUFACE AREA DEPENDENT ON SHAPE | Excel Discussion (Misc queries) | |||
Deleting a shape and the cell contents the shape is in. | Excel Programming | |||
Deleting a shape and the cell contents the shape is in. | Excel Programming |