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