Conditional Format Change Field Value

[leanbiz]

We're entering in part measurements, and I have a "OutOfSpec" field setup to indicate when a part is outside its tolerance range.



I'm trying to figure out the easiest way to set the "OutOfSpec" field when a value in another field is outside the tolerance ... is there a way to update a field when a conditional format is triggered? It seems like a waste to add VBA and do all tolerance checking again before inserting/updating the record when it's already done once with conditional format.

[JamesDeckert]

I guess you could check the background color (that you've changed with conditional formatting). You'd need to make sure you do the check after conditional formatting is done. Or maybe you could do the calc in OutOfSpec and conditional format on that field.

[leanbiz]

I'm not sure about that... I've tried checking the fields BackColor property and its always white (16777215), even when it displays Red on the screen because of conditional formatting.

[John_G]

...BackColor property and its always white (16777215)...

That's because when you check the colour it's looking at the setting in the BackColor property, which is not changed by conditional formatting.

As far as I know, conditional formatting does not trigger an event, so I think you will have to use VBA to set the value in the "OutOfSpec" field.