+ Reply to Thread
Results 1 to 7 of 7

Change Shape Color based on date

  1. #1
    Registered User
    Join Date
    12-22-2021
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    4

    Change Shape Color based on date

    Hello, help please!

    I want to create a code that reads the date in a cell (example "CA25") and changes the color of a shape "Bin R1" according to the following:
    if the cell is blank = shape color RGB (91,155,213)
    if the date in the cell is today and forward = shape color RGB (27,27,27)
    if the date in the cell is yesterday and before = shape color RGB (255,0,0)

    I have 76 shapes/bins that I need to use this code for. Is it possible to create a code for all 76 in one if each bin has a cell equivalent and shape name?

  2. #2
    Registered User
    Join Date
    09-10-2014
    Location
    Matrix
    MS-Off Ver
    2010
    Posts
    70

    Re: Change Shape Color based on date

    I made working sheet with the code below:
    Please Login or Register  to view this content.
    Please enter data in cell K2
    Attached Files Attached Files
    (*) Reputation points appreciated.
    excelbs.tk

  3. #3
    Registered User
    Join Date
    09-10-2014
    Location
    Matrix
    MS-Off Ver
    2010
    Posts
    70

    Re: Change Shape Color based on date

    Of course each shapes take into loop for each.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,115

    Re: Change Shape Color based on date

    you can use a user defined function. Function has a reference the to input date cell and the name of the shape to affect.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    12-22-2021
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    4

    Re: Change Shape Color based on date

    Hello,
    I'm getting this error message Attachment 761078 which directs me to this Attachment 761079

  6. #6
    Registered User
    Join Date
    12-22-2021
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    4

    Re: Change Shape Color based on date

    sorry that question was directed to michson.

  7. #7
    Registered User
    Join Date
    12-22-2021
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    4

    Re: Change Shape Color based on date

    I found a work around!

    I just conditionally formatted the cells and linked the display of the shape to the cell display.



    Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.Shapes("Cube 1").Fill.ForeColor.RGB = ActiveSheet.Range("CA25").DisplayFormat.Interior.Color

    End Sub

+ 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. Change Shape Color based on Cell Value
    By serenitylai in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2019, 03:25 AM
  2. Change the Color of Shape in Excel Based on Cell Value using VBA
    By kskhan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-17-2019, 10:57 PM
  3. Change Shape Color RGB based on Cells Color
    By jocker_boy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-30-2019, 05:31 AM
  4. VBA change shape color based on cell value
    By noelmus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-29-2018, 06:31 PM
  5. VBA: Change Shape Color based off cell value
    By emmz5d in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2016, 04:35 PM
  6. Shape Color Change Based on Value in different sheet
    By kevinspocket in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-17-2014, 05:50 PM
  7. change color of shape based on value in dropdownlist
    By raketman007 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2013, 09:39 AM

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