Conditional formatting based on two cells

Greymud

New Member
Joined
Feb 19, 2016
Messages
42
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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
ABC
1part%c
2Ford130.500Ford
3chevy140.750
4dodge150.825
Sheet4
Cell Formulas
RangeFormula
C2C2=IF(AND(LEFT(A2,4)="Ford",B2<0.8),"Ford","notford")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2Expression="AND(LEFT(A2,4)=""Ford"",B2<0.8)"textNO
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I think you have too many double-quotes in your Conditional Formatting formula.
It should just be:
Excel Formula:
=AND(LEFT(A2,4)="Ford",B2<0.8)

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


Note: Don't forget to set the red formatting highlight (believe it or not, sometimes people forget that part!).
 
Upvote 0
Solution
I think you have too many double-quotes in your Conditional Formatting formula.
It should just be:
Excel Formula:
=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:
Excel Formula:
=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))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top