Forum Discussion
Change an output based upon Conditional Formatting
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?
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
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.
- You are absolutely right .... the script should serve as a starting point for vba ... my failure not to write it down for that.
I'm not a VBA professional ... on the contrary, I am just as dependent on VBA as a beginner ... which I am in VBA too. Try to help with my ignorance. If I have insulted someone with it, please forgive me ... it was all done for good will. Beginners help beginners ... as far as possible, nothing more and nothing less.
That's why I publicly express my ignorance under my name.
Nikolino
I know I don't know anything (Socrates)
- ...maybe in VBA?
Sub Formatierung()
Dim oWS As Worksheet
Dim i As Integer
'Schleife über alle Tabellenblätter
For i = 1 To ThisWorkbook.Worksheets.Count
'Bereich für die Formatierung
With ThisWorkbook.Worksheets(i).Range("C:C,E:E")
'Regel setzen
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""ja"""
.FormatConditions(.FormatConditions.Count).Interior.Color = vbGreen
End With
Next i
End Sub
Nikolino
Ich weiß dass ich nichts weiß (Sokrates)
