Conditional formatting based on two cells

[Greymud]

Excel 2016

We're testing parts for different client and the data is in one spreadsheet for this test.

ColumnA has part numbers that all start with client identifier, ColumnB is where results are input as a number with 3 decimal points. Each client has a different acceptance range for test results. I want to have ColumnB highlight red if it's out of scope for that client.

To test my formula, I use ColumnC because I tend to use a period instead of a comma. In C2, is have this formula that works fine for what I'm trying to do. If the part number starts with that client identifier and is under a threshold, it says Ford. I can change the number and the client ID and get the proper reaction. I can string together the multiple AND statements with some OR statements with no issue

=IF(AND(LEFT(A2,4)="Ford",B2<0.8),"Ford","notford")

If I use the following for conditional formatting, it does nothing

AND(LEFT(A2,4)="Ford",B2<0.8)

Is there an issue with conditional formatting referencing it's own cell or something obvious I am missing?

TIA

_local archive Carburizing Metallurgical Record.xlsx
	A	B	C
1	part	%c
2	Ford13	0.500	Ford
3	chevy14	0.750
4	dodge15	0.825
Sheet4

Cell Formulas
Range	Formula
C2	C2	=IF(AND(LEFT(A2,4)="Ford",B2<0.8),"Ford","notford")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	Expression	="AND(LEFT(A2,4)=""Ford"",B2<0.8)"	text	NO

 

[Joe4]

I think you have too many double-quotes in your Conditional Formatting formula.
It should just be:

=AND(LEFT(A2,4)="Ford",B2<0.8)

When I do that, it works just fine for m:

Note: Don't forget to set the red formatting highlight (believe it or not, sometimes people forget that part!).

 

[Greymud]

I think you have too many double-quotes in your Conditional Formatting formula.
It should just be:

=AND(LEFT(A2,4)="Ford",B2<0.8)

When I do that, it works just fine for m:
View attachment 93420

Note: Don't forget to set the red formatting highlight (believe it or not, sometimes people forget that part!).

I don't know how that's changing. I copy/pasted part of the formula from C2. I try to correct it, but it errors out. Now the CF formula is

="AND(LEFT(A2,4)=""Ford"",B2<0.8)"

I think you have too many double-quotes in your Conditional Formatting formula.
It should just be:

=AND(LEFT(A2,4)="Ford",B2<0.8)

When I do that, it works just fine for m:
View attachment 93420

Note: Don't forget to set the red formatting highlight (believe it or not, sometimes people forget that part!).

I figured it out. I was not putting '=' before my CF formula and it was correcting (incorrecting?) for that. Thanks for pointing me in the right direction!

I'm now working on stringing these together for one cohesive CF formula and can't do the AND/OR combination right. Does this need to be a separate post? What I have now is:

=or(AND(LEFT(E2,6)="Chevy",Q2>=645,Q2<=760),and(LEFT(E2,4)="Ford",Q2>=655),and(LEFT(E2,3)="Dodge",Q2>=670,Q2<=780))

 

[Joe4]

I'm now working on stringing these together for one cohesive CF formula and can't do the AND/OR combination right. Does this need to be a separate post? What I have now is:

=or(AND(LEFT(E2,6)="Chevy",Q2>=645,Q2<=760),and(LEFT(E2,4)="Ford",Q2>=655),and(LEFT(E2,3)="Dodge",Q2>=670,Q2<=780))

That should work, if you want to Conditional Format it if any of those three combinations are met.
If it is not working, please show us a data sample that is not working right.