Conditional Formatting on a Drawing?

[Guest]

Is it possible to conditionally format a drawing? Or some other way that I
can accomplish the below task?

For example, I have a picture of a floor plan embedded within an excel
spreadsheet. In this floorplan, it has four offices. Within each office, I
used the "Draw" function to draw a circle which needs to be color coded
depending on occupancy. Each circle has been defined with a name. The color
coding of the circles are green for vacant, red for occupied, blue for common
area.

Then I have a separate list called Rent Roll, which is in Col A & B, where
column A lists the office # (1,2,3 and 4) and Col. B indicates whether it is
vacant, occupied or common.

Is there a way to make the circle change color depending on what is listed
on the Rent Roll without having to manually change the colors of the circle?

Thank you.

 

[Guest]

Here is a tiny VBA example that you can adapt to your needs.

First I drew four ovals using the drawing toolbar. Excel named them Oval 1,
Oval 2, Oval 3, and Oval 4.

I then fill A1 thru B4 with:

1 O
2 V
3 C
4 C

The run following macro to set the colors of the ovals:

Sub colorit()

statuss = Array("O", "V", "C")
ccodes = Array(17, 10, 12)

For i = 1 To 4
stats = Cells(i, 2).Value
ActiveSheet.Shapes("Oval " & i).Select
For j = 0 To 2
If stats = statuss(j) Then
Selection.ShapeRange.Fill.ForeColor.SchemeColor = ccodes(j)
End If
Next
Next
End Sub

 

[Guest]

Perfect. Thank you very much Mr. Gary

Here is a tiny VBA example that you can adapt to your needs.

First I drew four ovals using the drawing toolbar. Excel named them Oval 1,
Oval 2, Oval 3, and Oval 4.

I then fill A1 thru B4 with:

1 O
2 V
3 C
4 C

The run following macro to set the colors of the ovals:

Sub colorit()

statuss = Array("O", "V", "C")
ccodes = Array(17, 10, 12)

For i = 1 To 4
stats = Cells(i, 2).Value
ActiveSheet.Shapes("Oval " & i).Select
For j = 0 To 2
If stats = statuss(j) Then
Selection.ShapeRange.Fill.ForeColor.SchemeColor = ccodes(j)
End If
Next
Next
End Sub