Excel Help. Condition Formatting Drop down list.

[upinflamezzz]

Trying to help user see what trucks and trailers are used or out of service. Here's what I got so far.

B1 enter Tractors available, C1 enter Trailers available
F1 & K1 enter Availability. Merge G1&H1 enter Tractors, merge I1&J1 enter Trailers
In cols F and/or K enter NA against appropriate unit if out of service
In column H enter tractors available, in col I enter trailers available
In G2 =IF(OR(F2="NA",COUNTIF(B:B,H2)=1), H2&" N/A",H2) copy down list of tractors
In J2 =IF(OR(K2="NA",COUNTIF(C:C,I2)=1), I2&" N/A",I2) copy down list of trailers
Data validation col B select List, Source =$G$2:$G$20
Data validation col C select List, Source =$J$2:$J$20

This works by placing a N/A next to the truck or trailer. I want trucks and trailers in the drop down list to format red if it contain N/A. How can I accomplish this?

 

[SkipVought]

Hi,

The drop down cannot be formatted.

You could concatenation the list & N/A column for you Data Validation list and thereby see the N/A in the drop down.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[upinflamezzz]

That already happens. How can add different reason codes in addition to NA, I want OOS (out of service), TD (trailer drop) and LOS (left on street) to be acceptable reason codes instead of just NA.

What does this line become then?

IF(OR(F2="NA",COUNTIF(B:B,H2)=1), H2&" N/A",H2)

 

[SkipVought]

You ought to know what it returns.

Make a unique list of 2 columns that include your codes

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}