Conditional Formatting for a Drawing Object?

Topic

New Reply

Greetings –

I’m wondering if there’s a way to change the color of a drawing object based on the value of another cell – essentially the same functionality as “Conditional Formatting” but for a fill color of an object rather than for a cell?

I can probably think of ways to simulate this behavior – but I’m up for ideas on that as well!

Thanks much,
Angela

Reply | Quote

Replies

There is no direct equivalent, but you could use code in the Worksheet_Change event of the worksheet to change the fill color depending on the value of a cell, for example

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("A1"), Target) Is Nothing Then
	With Me.Shapes("Rectangle 1").Fill
	  If Range("A1") > 10 Then
		.ForeColor.RGB = vbRed
	  Else
		.ForeColor.RGB = vbGreen
	  End If
	End With
  End If
End Sub

In this code, “Rectangle 1” is the name of the shape. You can see the name of a shape if you select it, in the name box on the left hand side of the formula bar.

Reply | Quote

that’s it – lovely! and – I know I could find this if I look around, but if you know off the top of your head:
– is there a way to get it to update automatically, or will the end user always need to click in “A1” to get the color to update?
– is there a listing somewhere of VBA colors?

Many, many thanks!
Angela

Reply | Quote

The idea is to let the code check all cells that the color will depend on – in the example, it’s just cell A1 but it could be more cells if you have complex conditions.
Once you’ve written the code, the color of the shape will change automatically when the user changes the value of one of those cells.

This won’t work for cells containing formulas, you should monitor the cells that are changed by the user and that contribute to the conditions. If that is not possible, you could use the Worksheet_Calculate event, but that has a higher overhead because it occurs for EACH calculation on the worksheet.

There are only 8 named VBA colors – see the Object Browser (press F2):
vbWhite, vbBlack, vbRed, vbGreen, vbBlue, vbYellow, vbCyan and vbMagenta.
You can specify any RGB color in the form RGB(x, y, z) where x, y and z are values in the range 0…255.
But Excel 2003 and before use a color palette of only 56 colors, so the RGB color that you specify will be mapped to the nearest palette color.

Reply | Quote

ok, that’s odd – even when I directly change the value of a cell, the color doesn’t change until I click back on it…

Reply | Quote

Perhaps you used Worksheet_SelectionChange instead of Worksheet_Change?

I have attached a small sample worksheet. If you change the value of cell A1 to a value greater than or smaller than 10, the oval shape should change color automatically (you have to enable macros, of course).

Reply | Quote

That’s exactly what I did – somehow. Thank you!

Reply | Quote