Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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).
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's exactly what I did - somehow. Thank you!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •