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.