Jul 14 2020 09:25 AM
We have a series of workbooks that utilize conditional formatting to highlight when a value exceeds a given tolerance range (cell background turns red). I would like to be able to change a text string on the 1st sheet of each workbook from one value to another if any cell in the same workbook is turned red by this conditional formatting. I've been searching, but can't seem to find anything that will accomplish this, even in VBA. What am I missing? Thanks in advance for your help!
Jul 14 2020 10:03 AM
In the absence of seeing your actual workbooks, I can only offer a possible resolution.
It's this: in order for the conditional formatting to be working, to turn those cells to red, there has to be a formula or function that underlies that conditional formatting. Just use those same formulas/functions as the basis for changing the text in the desired cells. Don't look for "if red, then change text"; rather, whatever those conditions/rules that cause the red should be able to be employed to change the text.
Or am I missing something?
Jul 14 2020 10:16 AM
Thank you for the quick reply. I'm not sure what I can and can't share from a confidentiality standpoint. We utilize a 2 workbook approach for each of our parts. The first workbook is a simple data table that is output by our CMM. The second workbook "maps" the data from the the 1st workbook's table into a more easily digestible format. Picture a workbook that attempts to visualize measurements around a part. The part is visualized by cross-sections on the page, and "overlaid" by the difference between the nominal value and the measured value of a feature or dimension. These values will be color-coded green, yellow, or red as they near the limits of the tolerance (via conditional formatting). A fourth conditional formatting rule will turn the cell of the value red to highlight that it is out of specification and manufacturing needs to halt until this is corrected. Some measurements may have a tolerance range of +/- 1mm, some may be +/- 1.5mm, etc. So there's not a hard-and-fast value around which I can write a single rule for the workbook. What I need is something that can search the workbook (typically 2 or 3 worksheets) and find any cells that were highlighted red, and then turn a "text flag" on the 1st worksheet from "OK to run" to "Do not run!". @mathetes
Jul 14 2020 10:44 AM
Jul 14 2020 01:36 PM
You wrote: These values will be color-coded green, yellow, or red as they near the limits of the tolerance (via conditional formatting). A fourth conditional formatting rule will turn the cell of the value red to highlight that it is out of specification and manufacturing needs to halt until this is corrected. Some measurements may have a tolerance range of +/- 1mm, some may be +/- 1.5mm, etc. So there's not a hard-and-fast value around which I can write a single rule for the workbook.
And from that reference to changing colors based on how close a particular measurement gets to the tolerance limits, I assume that there's also a table with those tolerance limits, (maybe with product specific values), somewhere in this workbook. And the same for that "fourth formatting rule"--it has to be referring to a table of values......in order to do what you're describing.
So even if there's not a single hard-and-fast value, there are values specified somewhere in there...just waiting to be put to work.
Jul 14 2020 02:04 PM