Shading Shapes

[Hood]

I am trying to change the colors of some shapes based on a cell value like the code below

Please Login or Register  to view this content.

I have an unknown number of shapes as well as an unknown number of sheets. One document may have thirty shapes, while the next may have two hundred shapes.

Each shape is numbered such as Oval1, Oval2, Oval3, etc. In Column A are numbers that refer to the shapes. So if cell A11 contains the number “1”, it refers to the shape “Oval1”. If cell A13 contains the number “2”, it refers to the shape “Oval2”.

In Column O of the same row is the cell that I am checking for the “X” that determines the color of the cell.

While the numbers are consecutive, the rows are sometimes skipped. So Cell A13 may contain the value “2” (which refers to “Oval2” and I am checking Column O for the “X” in row 13). Cell A20 may contain the value 3 (which refers to “Oval3” and I am checking Column O for the “X” in row 20).

Because it skips, I never know for sure what sheet contains the data for the specific shape. I need something that can check column O for the letter “X” and change the corresponding shapes color for each sheet. All the shapes are located on the sheet named "Print"

Any help would be appreciated.

Thanks

[romperstomper]

If I understood that correctly, then:

Please Login or Register  to view this content.

(air code BTW so needs to be checked!)

[Hood]

Thanks so much for the help and quick response. A quick check of the code you provided seems to be very close. The one problem I noticed is that it only seems to work when I manually enter the "X" in the O column.

Column O contains a formula which dictates whether or not it shows an X in the cell. Any idea?

Thanks again!!!

[romperstomper]

The Change event doesn't work with formulas. You would need to either use the Calculate event, which doesn't give you any method of determining why the calculation occurred, or you would need to monitor whatever cells are the inputs for the formula in O.

[Hood]

I don't quite understand. The shapes will change color using the code I originally posted, which is a Change Event. How else might I go about this?

[romperstomper]

That's because your Change event doesn't actually check to see what was changed, wheres mine was checking to see if the change occurred in column O, and hence just changing the shape for that changed row. If you want to recolour all the shapes regardless whenever you change anything on any sheet, I think it might bog your workbook down.
Does the formula in O depend on cells on the same sheet?

[Hood]

Yes it does. Actually, it's a quite simple formula. It also refers to cells in the same row.

=IF(G11="","",(IF(M11="X","","X")))

[romperstomper]

Are G and M manually entered data?

[Hood]

No, they are not. There isn't anything that is manually entered. It's data from a separate file that is inputed through a macro.

[romperstomper]

It's data from a separate file that is inputed through a macro.

When I said manually entered, I basically mean anything that isn't a formula. If the macro enters the values, that would also trigger the change event. However if you have a macro doing that, it might be easier to add the colouring code to that macro? If not, try this version:

Please Login or Register  to view this content.

[Hood]

That didn't quite work either. Someone else told me I should try try to use a Macro using Find Next to locate the "X". But I haven't had any success with that either.
With Worksheets("Print1").Shapes(bn)
If c.Column = 13 Then .Fill.ForeColor.SchemeColor = 3
If c.Column = 15 Then .Fill.ForeColor.SchemeColor = 2
End With

[romperstomper]

That didn't quite work either.

in what sense?

[Hood]

I still had to manually enter the "X"

[romperstomper]

Does the macro you mentioned enter the values into columns G and M?

[Hood]

The macro copies data from another workbook and will paste it into the G - L columns. Column M is a formula based upon whether or not the data is within a specified tolerance.

[romperstomper]

You could try the Calculate event instead:

Please Login or Register  to view this content.

[Hood]

It seems to have a problem with this line..

Worksheets("Print").Shapes("Oval" & Cells(lngRow, "A").Value).Fill.ForeColor.SchemeColor = lngColour

[romperstomper]

What is the error?

[Hood]

Item with specified name not found.

But the shape is there..

[Hood]

I think it's looking for the next shape instead of ending

[romperstomper]

It's only looking for shapes where the value in column O is an X.

[Hood]

Whenever I change the data in the cells to get the "X" in column O it hangs up on this line: Worksheets("Print").Shapes("Oval" & Cells(lngRow, "A").Value).Fill.ForeColor.SchemeColor = lngColour

The error is "Item with specified name not found. It does change the shape color for the correct shape, but it gives this error.

[Hood]

The problem is that sometimes there are X's in the O column, when there isn't a number in the A column. In this case, I need it to skip the row.

Maybe it would be easier to check for a value in the A column and then check the O column for an X?

Thanks again for all the help!

[romperstomper]

Variation on a theme:

Please Login or Register  to view this content.

[Hood]

That works! Thanks very much!!

I would like to try and tweak it for one more thing if possible.

I would like the code to check the blank cells in column A, check for the ‘X’ in the same row, and refer it to the Oval Shape for the number above.

For example:
A11 = 1

A15 = 2

A20 = 3

A21= 4

Then:
Any ‘X’ in O11:O14 shades Oval1

Any ‘X’ in O15:O19 shades Oval2

Any ‘X’ in O20 shades Oval3

Etc.

Thanks very much for all the help!!!!!

[Hood]

Just thought I would try again to see if there is a fix to this last problem.
Thanks!

[romperstomper]

Untested:

Please Login or Register  to view this content.

[Hood]

That didn't work.

It only seems to work if the last row contains an "X" before the next number in the A Column.

When I want it to check for an "X" in the any of the rows between the two numbers. It also did not work for the last row.

Thanks Again for helping. I would really be thankful if you could help me get this last part to work.

[romperstomper]

Ah - it's more complicated than I first thought. Don't really have time to do that today. Maybe next week.

[Hood]

Alright, thanks again!

[Hood]

Any other thoughts?

Could I do an offset, something like this?

Please Login or Register  to view this content.

[shg]

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.

[Hood]

Sorry, I forgot to add the code tags.
Thanks