shape colors dependent on calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Papa:

Shapes are one of the MS Excel collections, just like cell comments.
You'll have to have a process that identifies each shape so that you can
relate it to its data cell. That is, what relates Shape(14).AnyProperty to
cell Q19? Once you make that relationship, you'll need code to pull the
conditional format color and update the Background property of the shape.
I've done this with cell comments in the past but shapes are not
cell-dependent so if you resize a column, for example, the pointer and the
position of the shape doesn't change. Excel HELP will give you some pointers
for navigating through shapes and, also, how to work with the results of
conditional formats.

Steve in Ohio
 
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
 
I believe I understand what you said. However what I need to know so that I
can specify the color property for the shape is:
How can I "read" the color of a cell so that I can tell the shape to mimic
that color.
My conditional formatting tells a cell to turn, for example, red. Then I
want the shape to also become red, but I don't know how to identify the
(displayed) colorindex of the cell.
 
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.
 
Back
Top