Conditional Formatting on a Drawing?
 

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.

Conditional Formatting on a Drawing?
 

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


--
Gary''s Student - gsnu200752


"Storm" wrote:

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.

Conditional Formatting on a Drawing?
 

Perfect. Thank you very much Mr. Gary

"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200752


"Storm" wrote:

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.