+ Reply to Thread
Results 1 to 2 of 2

Shape Colors Updated per Cell Value (150 cells and shapes)

  1. #1
    Registered User
    Join Date
    01-19-2023
    Location
    NY, USA
    MS-Off Ver
    Microsoft Excel 2019 for Windows
    Posts
    2

    Shape Colors Updated per Cell Value (150 cells and shapes)

    I've found related projects and issues but I am new to VBA and am struggling, though I have managed to make the below code work.
    In my project, column A has all lot #'s and column C shows current project status. Each lot has a shape shown on a map and each status designation is assigned a separate color.
    The code below works, but I need to apply this to 150 cells and corresponding shapes (that show up on the map).
    Thank you to anyone who might help with this. I've attached a file with the first 8 shapes & cells...


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("C2") = "Rough Framing" Then
    ActiveSheet.Shapes.Range(Array("Lot394Shape")).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(252, 193, 106)
    Else
    If Range("C2") = "Permitting" Then
    ActiveSheet.Shapes.Range(Array("Lot394Shape")).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(250, 132, 108)
    Else
    If Range("C2") = "C/O" Then
    ActiveSheet.Shapes.Range(Array("Lot394Shape")).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(123, 242, 76)
    Else
    If Range("C2") = "Rough Mechanicals" Then
    ActiveSheet.Shapes.Range(Array("Lot394Shape")).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(252, 242, 106)
    Else
    If Range("C2") = "Finish Mechanicals" Then
    ActiveSheet.Shapes.Range(Array("Lot394Shape")).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(187, 253, 228)
    Else
    If Range("C2") = "Drywall" Then
    ActiveSheet.Shapes.Range(Array("Lot394Shape")).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(110, 186, 248)
    Else
    If Range("C2") = "Painting" Then
    ActiveSheet.Shapes.Range(Array("Lot394Shape")).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(208, 175, 235)
    Else
    If Range("C2") = "Punchout" Then
    ActiveSheet.Shapes.Range(Array("Lot394Shape")).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(243, 115, 191)
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End Sub
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-19-2023
    Location
    NY, USA
    MS-Off Ver
    Microsoft Excel 2019 for Windows
    Posts
    2

    Re: Shape Colors Updated per Cell Value (150 cells and shapes)

    PLEASE NOTE:
    I had not realized cross-posting was discouraged. This post is also being discussed on VBA Express. I will link the thread if there's any development here, thank you for your time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 10-01-2020, 05:40 AM
  2. Trying to get shape colors to change based on a cell value using VBA.
    By jaxx325 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-07-2017, 09:43 PM
  3. Changing cells and column chart colors based on cell colors
    By HDeuce in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-03-2016, 11:43 AM
  4. Replies: 10
    Last Post: 11-22-2013, 04:58 AM
  5. Hide shape based on cell value - For mutliple shapes
    By [Jimmy] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2012, 09:36 AM
  6. Draw shapes on a shape: Is it possible?
    By Hopworks in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-26-2011, 03:55 PM
  7. Colors of shapes and cells
    By mike cook in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-01-2008, 10:59 AM

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