+ Reply to Thread
Results 1 to 3 of 3

Make autoshape automatically change color

Hybrid View

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    59

    Make autoshape automatically change color

    Hi everyone,
    I am currently working on a small project and I need an autoshape to automatically change color based on a cell value. I have an attached example and in this case it's cell B8. B8 sums up data from cell B3:B6. I've created the code, but the autoshape won't automatically change color. I need to double click on B8 and press enter, then the color changes.
    Any ideas what might be wrong?

    Here's my code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    'Macro purpose: Make autoshape change color based on value in cell B8
        If Not Intersect(Target, Range("B8")) Is Nothing Then
                   If IsNumeric(Target.Value) Then
                If Target.Value < 10 Then
                    ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbGreen
                ElseIf Target.Value >= 20 And Target.Value < 30 Then
                    ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbYellow
                Else
                    ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbRed
                End If
            End If
        End If
    End Sub
    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Make autoshape automatically change color

    Use the Calculate event instead. See the attached.
    Attached Files Attached Files
    Gary's Student

  3. #3
    Registered User
    Join Date
    08-30-2012
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Make autoshape automatically change color

    It worked like a charm.

    Thanks so much,
    Amar.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1