Shape coloring macro tied to formula

[dreddster]

Hey guys,

This is probably something easy but I can't figure it out. I have this macro to color shapes depending on the value

Please Login or Register  to view this content.

Works fine when I enter values from 1 to 100 at T5, U5, V5. However these cells should be formulas and when I change them, coloring stops working.(for example this is the formula for T5: =IFERROR((($T$2/$T$3)*6.25)/100; "")

How do I fix this?

Thank you!

[JosephP]

formula calculations do not trigger the change event so you would have to use the calculate event and check all the cells each time it triggers (there is no Target argument for the Calculate event)

[dreddster]

I think you overestimated my knowledge on VBA What I understand from your response is that this macro wont work with formulas, and I should look for a macro that starts with Worksheet_calculate to do the job, right? Oh, well I will look for one now.

Thank you Josie.

[JosephP]

Please Login or Register  to view this content.

for instance :-)

[dreddster]

This is awesome, works like a charm!

Thank you so much Josie!

[JosephP]

you're welcome :-)

[dreddster]

Hmm, one more question.
When I open another excel file I get an error, as it seems the new file runs the macro from this workbook.

How do I limit this macro only to this file? (Also I change the names all the time like V22, V23 etc., if that can be taken into account).

[JosephP]

perhaps

Please Login or Register  to view this content.

not sure what you meant about changing the names-what names?

[dreddster]

Now it is perfect!

Thank you once again for your help