+ Reply to Thread
Results 1 to 9 of 9

Change color of shapes based on cell values.

  1. #1
    Registered User
    Join Date
    04-23-2013
    Location
    Houston, Tx
    MS-Off Ver
    Excel 2010
    Posts
    4

    Change color of shapes based on cell values.

    Hello All,

    I am working on a KPI document that requires a "stoplight" color to indicate the health of each vessel in an oil system based on the score it is given ( 0=white,3=red,2=yellow, Everything else=green). I am attempting to use the code below, and it has worked on ONE vessel in another document, however I do not know how to repeat the vba instructions properly to make it test each cell/vessel. Also I made a test document to show you what I'm working with and it will now not work for this document. I have renamed all of the shapes to their vessel names. Please see attached spreadsheet for the test. ExcelForumsTest.xlsm Any help on this would really help me out, I am new to VBA but trying to learn as quickly as possible. Thank you!

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("b2")) Is Nothing Then
    If IsNumeric(Target.Value) Then
    If Target.Value = 0 Then
    ActiveSheet.Shapes("Hp#2Separator").Fill.ForeColor.RGB = vbWhite
    ElseIf Target.Value = 3 Then
    ActiveSheet.Shapes("Hp#2Separator").Fill.ForeColor.RGB = vbRed
    ElseIf Target.Value = 2 Then
    ActiveSheet.Shapes("Hp#2Separator").Fill.ForeColor.RGB = vbYellow
    Else
    ActiveSheet.Shapes("Hp#2Separator").Fill.ForeColor.RGB = vbGreen
    End If
    End If
    End If
    End Sub

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Change color of shapes based on cell values.

    Hi,

    actually your code works perfectly fine, but you cannot see the different color in the shape because the shapes have no filling. They all have a transparent background set

    Regards
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Change color of shapes based on cell values.

    this works
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-23-2013
    Location
    Houston, Tx
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Change color of shapes based on cell values.

    Wow, didnt even catch that thanks! BUT how do I link more than one together? I want it to be able to check cells B2, B3, B4, and B5 and change the shapes associated accordingly. Do i need to just put the macro again below it?

  5. #5
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Change color of shapes based on cell values.

    Hi,
    this works for all 4 cells and all 4 shapes:
    Please Login or Register  to view this content.

    Regards

  6. #6
    Registered User
    Join Date
    04-23-2013
    Location
    Houston, Tx
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Change color of shapes based on cell values.

    Just one more question for you guys. Say I wanted to put the shapes in a separate sheet so that I could easily Export just those objects to PDF from the sheet, but wanted to keep the data in the current sheet is in now. How would I write this in the code to tell excel to change the color of the shapes based on the value in another sheet? I'm assuming the code would be generally the same, however I would have to specify that the target range is in another sheet? If so, how would this be written? The name of my data sheet is "Dashboard". The name of my shapes sheet is "Copied PFD". Thanks for all your help so far!

  7. #7
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Change color of shapes based on cell values.

    Hi,

    the reference to the sheet is set here:
    Please Login or Register  to view this content.
    so if the shapes are in sheet "Copied PFD" then:
    Please Login or Register  to view this content.
    and the code must be in the Worksheet-module of the sheet "Dashboard"

    Regards

  8. #8
    Registered User
    Join Date
    04-23-2013
    Location
    Houston, Tx
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Change color of shapes based on cell values.

    So, for one vessel it would look like this?
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("z6:z8")) Is Nothing Then
    If IsNumeric(Target.Value) Then
    If Target.Value = 0 Then
    With Worksheets("Copied PFD").Shapes("3A3B").Fill.ForeColor.RGB = vbWhite
    ElseIf Target.Value = 3 Then
    With Worksheets("Copied PFD").Shapes("3A3B").Fill.ForeColor.RGB = vbRed
    ElseIf Target.Value = 2 Then
    With Worksheets("Copied PFD").Shapes("3A3B").Fill.ForeColor.RGB = vbYellow
    Else
    With Worksheets("Copied PFD").Shapes("3A3B").Fill.ForeColor.RGB = vbWhite
    End If
    End If
    End If

  9. #9
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Change color of shapes based on cell values.

    No, and please use the Code-tags to post Code: CODE-Tags

    Let me explain the code I posted before line by line, so you can understand and adapt it to your needs
    Please Login or Register  to view this content.
    So, if you have the shapes in a specific table which is not the same as the values (0,1,...) you enter then you have to change the sheet reference, in the code it is "Activesheet" to Worksheets("SHEETNAME")

    Regards

+ 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