+ Reply to Thread
Results 1 to 10 of 10

Looping Sheets to change Color of Shapes

  1. #1
    Forum Contributor
    Join Date
    09-25-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    152

    Looping Sheets to change Color of Shapes

    I have a workbook that I use as a Master Template for other jobs.
    * The workbook contains several sheets.
    * The number of sheets will vary depending on the job.
    * There is a sheet in the work book called "Print" where a number of shapes are stored.
    ** The number of shapes will vary depending on the individual workbook.
    **Each Shape has a name that increments by 1. Example Oval1, Oval2, Oval3, etc.

    Starting with Sheet1:
    * There are numbers in column A11:A38 (Columns AB are merged together)
    ** These numbers refer to the Oval Shape.
    ** Example: On sheet 1 in cell A11 will be number 1, which refers to Oval1. On sheet 1 in cell A12 is number 2, which refers to Oval2, etc.

    * In column O11:O38 (Columns OP are merged together)
    ** If there is an "X", I want the Shape to be Green
    **Ex: Sheet1 Cell(A15).Value = 5 and Cell(O15).Value = "X" then Shape.Oval5 = Red, Else Shape.Oval5 = Green
    **Ex: Sheet2 Cell(A18).Value = 20 and Cell(O18).Value = "X" then Shape.Oval20 = Red, Else Shape.Oval5 = Green

    I would like the code to loop through all the sheets and evaluate the condition and change the color for each individual shape as desired.
    A couple of problems that I would like solved as well if possible:
    **I prefer the code to update when the cell changes and not a Macro to be ran each time. In other words. If a user changes the cell value in O15 to an X, I would like the cell to change color at that time.
    **Even more difficult, I would like help to resolve this issue as we (if possible): In many cases the shape is evaluating several conditions as well. It's kind of hard to explain, but I will try.
    **Ex: Sheet1 Cell(A11) = 1 *Oval1 evaluates cells M11:M14. If there is an "X" in cells O11:O14, make Oval1 red, else make it green.
    Sheet1 Cell(A15) = 2 *Oval2 evaluates cells M15:M17. If there is an "X" in cells O15:O17, make Oval2 red, else make it green.
    Sheet1 Cell(A18) = 3 *Oval3 evaluates cells M18:M20. If there is an "X" in cells O18:O20, make Oval1 red, else make it green.
    Sheet1 Cell(A20) Etc.
    **Note: There are formulas in column O to evaluate whether or not there should be an "X" in the cell.

    I will try to attach the workbook file to this post to help explain. Also, originally I am evaluating Column "M" for green and Column "O" for red, but thought it would be easier to just do an or else statement. I have posted my code that I have been manually modifying to work...

    Thanks for any help or advice!

    Here is what I have started with, not even close to what I would like, but a start...

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Looping Sheets to change Color of Shapes

    Try this in the "ThisWorkbook" module:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Contributor
    Join Date
    09-25-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: Looping Sheets to change Color of Shapes

    Thank you for your help!
    It is very close to what I am looking for. I would like it to do one other thing.
    I would like it to evaluate for an "X" in any cell referring to the Oval Shape

    For example:
    **Sheet1 Cell(A11) =
    **Oval1 evaluates cells O11:O12. If there is an "X" in any of the cells O11:O12, make Oval1 red, else make it green.
    Sheet1 Cell(A13) = "2"
    **Oval2 evaluates cells O13:O15. If there is an "X" in any cells O13:O15, make Oval2 red, else make it green.

    Currently the code you supplied appears to check the last row for the oval shape. In the examples above, Oval1 will turn red only when there is an "X" in cell O12, I would like it to turn red if there is an "X" in either O11 and/or O12.

    Thanks again for the help, I appreciate it very much!!!!

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Looping Sheets to change Color of Shapes

    Hi Hood,

    Thanks for the rep!

    Maybe:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    09-25-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: Looping Sheets to change Color of Shapes

    Thanks for the response. I tried the code, but it still seems to be checking the last row to evaluate. I don't quite understand the code you posted and I'm not sure how to modify to get it to work the way I would like it to. Thanks again for all your help, I appreciate it very much.

  6. #6
    Forum Contributor
    Join Date
    09-25-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: Looping Sheets to change Color of Shapes

    Would I need something to loop through the individual ranges that are created? Just thinking out loud, thanks.
    But then would it change it to green if the last condition was good?

    Please Login or Register  to view this content.

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Looping Sheets to change Color of Shapes

    Hi Hood,

    You need to have the calculation set to automatic for the routine to effect.

    Try that and then let me know:|

    Please Login or Register  to view this content.
    Last edited by xladept; 04-13-2015 at 02:29 PM.

  8. #8
    Forum Contributor
    Join Date
    09-25-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: Looping Sheets to change Color of Shapes

    Yes, I checked that as well, but no luck....

  9. #9
    Forum Contributor
    Join Date
    09-25-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: Looping Sheets to change Color of Shapes

    Any other ideas? It just seems to be checking the last line for an "X" instead of checking the range of cells where the "X" is located for the specific shape. If you look at Number 11 on the first page of the file I have attached, you will notice one line where there is an "X", so shape "Oval11 should be red.
    Thanks!
    Attached Files Attached Files
    Last edited by Hood; 04-16-2015 at 08:10 AM.

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Looping Sheets to change Color of Shapes

    Try this code - replace all the code you have in the "ThisWorkbook" module with this code:

    Please Login or Register  to view this content.
    And let me know how it works out

+ 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. Query change the Shapes color by entering value in cell
    By ashokJan in forum Excel General
    Replies: 5
    Last Post: 12-05-2014, 07:09 AM
  2. Change color of shapes based on cell values.
    By rkostner in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-24-2013, 05:55 PM
  3. Looping through Shapes to change transparency
    By JoeSkittles in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-24-2013, 11:09 AM
  4. [SOLVED] Change color of text for a group of shapes
    By emiliekatherine in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2012, 08:43 AM
  5. Replies: 1
    Last Post: 04-13-2006, 07:30 PM

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