Conditional Formatting using color doesn't work in some cells

roc_ent

Board Regular
Joined
Jan 27, 2003
Messages
246
Office Version
  1. 2019
Platform
  1. Windows
I've created an order book that cells change with conditional formatting but, the rules are not working in specific cells. In my workbook, a dropdown list is in M2, (Shipped, Tracking, Received, Damaged, Claim, Closed) are my options.
When item is shipped cells B2 and C2 are highlighted in Blue (=$M$2="Shipped") condition is a light blue, this works good
When a tracking number is available C2 and D2 in a darker blue ($M$2="Tracking") condition highlighted cells C:D2
For receiving the merchandise Cells B to F highlights in another color, closed highlights cells A2:K2 and no problem. Now when items are damaged, or short, I'm trying the same formula to highlight cell E,F,G2 to go in red but although I use the same formulas as the others it won't work. Any ideas as to why it won't do the damaged .
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,741
Office Version
  1. 365
Platform
  1. MacOS
what order are the formulas in ?
Use stop if true
you are using different range selections for different words in M2 as well

6 rules
M2 = Shipped, - range B2:C2 - Light Blue
M2 = Tracking, - range C2:D2 - Darker Blue
M2 = Received, - range B2:F2 - Another colour
M2 = Damaged, - range E2:G2 - RED
M2 = Claim, - range B2:C2 - ???
M2 = Closed - range A2:K2 - Another colour

I have just tried the 1st 2 rules and they work OK
Book2
ABCDEFGHIJKLM
1
2shipped
3
4M2, (Shipped, Tracking, Received, Damaged, Claim, Closed) are my options.
5When item is shipped cells B2 and C2 are highlighted in Blue (=$M$2="Shipped") condition is a light blue, this works good
6When a tracking number is available C2 and D2 in a darker blue ($M$2="Tracking") condition highlighted cells C:D2
7For receiving the merchandise Cells B to F highlights in another color, closed highlights cells A2:K2 and no problem. Now when items are damaged, or short, I'm trying the same formula to highlight cell E,F,G2 to go in red but although I use the same formulas as the others it won't work. Any ideas as to why it won't do the damaged .
8
9
10
11M2 = Shipped, - range B2:C2 - Light Blue
12M2 = Tracking, - range C2:D2 - Darker Blue
13M2 = Received, - range B2:F2 - Another colour
14M2 = Damaged, - range E2:G2 - RED
15M2 = Claim, - range B2:C2 - ???
16M2 = Closed - range A2:K2 - Another colour
17
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:D2Expression=$M$2="tracking"textNO
B2:C2Expression=$M$2="shipped"textNO
 
Last edited:
Solution

roc_ent

Board Regular
Joined
Jan 27, 2003
Messages
246
Office Version
  1. 2019
Platform
  1. Windows
Thank you for your advice well appreciated.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,741
Office Version
  1. 365
Platform
  1. MacOS
has that helped solve the issue ?
If not, let me know and i can setup all the rules - Claim Needs a range and colour
 

Watch MrExcel Video

Forum statistics

Threads
1,132,645
Messages
5,654,556
Members
418,140
Latest member
ahepple86

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
Top