Conditional Formatting Formulas

Copper Contributor

I'm looking for help on a formula to apply conditional formatting to cells font color based on a cell +/- a tolerance in a first article sheet used in a metal or plastic manufacturing setting, can you help?

13 Replies

@DanMRaycap 

Could you give a sample how it looks like?

 

1*14.650"+/-0.007"14.66514.65014.65014.65014.65014.6530.006708  

@DanMRaycap 

 

14.650"+/-0.007"14.66514.65014.65014.65014.650

@Sergei Baklan 

@DanMRaycap 

 

That could be like

image.png

Please check attached.

Thank you that's close, so I'm trying to make any of the 5 measurement cells turn red font IF they are Out of tolerance.  IE  A1 +/- .0007 = Red font IF not font stays Black.

 

DIMENSION/SPECIFICATIONSUPPLIER MEASUREMENTS
PART 1PART 2PART 3PART 4PART 5
14.650"+/-0.007"14.66514.65014.65014.65014.650

 

@Sergei Baklan 

@DanMRaycap 

 

Where is A1 in this sample? And here only the cell under PART 2 shall be in red, correct?

Ok A1 is Dimension lets make it 1.0 and tolerance is +/- .0007.

So IF Measurement is In between  0.993‬ and 1.007‬ Font in the cell should remain, Black, IF the dimension is Not in the range it should turn Red for all 5 cells called supplier measurements, does that help?   @Sergei Baklan 

=IF(D10>($B$10+$C$10),Red,))IF(D10<($B$10+$C$10),Red,))

@DanMRaycap 

 

Nope, that fully different from your first sample and looks like

image.png

Is that one is the basis for the formula?

 

ITEMDIMENSION/SPECIFICATIONSUPPLIER MEASUREMENTSAVERAGESTD DEVACCREJMETHOD or GAUGEASSET#
PART 1PART 2PART 3PART 4PART 5
1*14.650"+/-0.007"14.66514.65014.65014.65014.65014.6530.006708  DC 

In the example above Part#1 should have Red font if the formula is working correctly. @Sergei Baklan 

@DanMRaycap 

Okay, like this

image.png

In A3 we have text "1*" and in C3 text "+/-0.007", in other cells in this row are numbers. Based on which formula we shall compare texts and numbers?