I have a series of abbreviations in Column A

CB, CH

PL

CB, PL

CB, CH

ES

CH

DC, CH

DC, KK

CH

KD

CB, CH

ES

Ideally, I would like to make a conditional format....

if A2= "CB", "PL", "KD", "ES", "CZ", or "GG" then I want the cell Blue (Note: these abbreviations represent "ML")

if A2= "DC", "CH', "RN", "MB" or "DW" then I want the cell Yellow (Note: these abbreviations represent "PC"If A2= a combinations of both (ex: CB, CH) then I want the cell to be green. Esentially the formula would be if "ML" and "PC" are in the same cell the cell would be "SHARED"

I hope this makes sense. Please help!

~Rose

How does KK fit into this? The fact that these abbreviations represent "ML" and "PC" doesn't really matter to us, does it?

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

I set up a spreadsheet as follows. The ranges you use don't matter as long as they match the ranges you reference in your Conditional Formatting formulas. M N 3 CB DC 4 PL CH 5 KD RN 6 ES MB 7 CZ DW 8 GGCF formula for

Blue:=NOT(ISNA(VLOOKUP(A2,$M$3:$M$8,1,0)))

CF formula for

Yellow:=NOT(ISNA(VLOOKUP(A2,$N$3:$N$7,1,0)))

CF formulas for

Green:

(You need 2 if you want to be able to check for pairs in both orders e.g.CB, CHandCH, CB)Note: Both formulas are split into 2 lines for ease of reading in this forum.

=AND(NOT(ISNA(VLOOKUP(LEFT(A2,2),$M$3:$M$8,1,0))),

NOT(ISNA(VLOOKUP(RIGHT(A2,2),$N$3:$N$7,1,0))))=AND(NOT(ISNA(VLOOKUP(RIGHT(A2,2),$M$3:$M$8,1,0))),

NOT(ISNA(VLOOKUP(LEFT(A2,2),$N$3:$N$7,1,0))))

If you don't understand what I'm doing with these formulas, just ask - after you've tried to figure them out.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Hello Derby Dad, I used your Conditional Formula for both blue and yellow and nothing is happening?

Correct me if I'm wrong, but I highlighted all data from A3:A510 and clicked Insert>Conditional Format and typed your formula with the exception of my VLookup which would be $C$517:$C$522 (for blue).A B C D

3 CB, CH

4 PL

5 CB, PL

6 ES

7 DC

~~~~

(VLookup data on same spreadsheet)

516 ML PC

517 CB DC

518 PL CH

519 KD RN

520 ES MB

521 CZ DW

522 GG

~~~~~

As for KK: technically he represents both ML & PC which would make him GreenAny other suggestions, or I am typing something incorrectly?

Thank your for your help!

~Rose

Please read the following line and repost your data to make it easier for us to determine what column contains what data. (Click on the words "How To") Your data should end up looking something like what I posted in Response #2.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Hello Derby Dad, I used your Conditional Formula for both blue and yellow and nothing is happening?

Correct me if I'm wrong, but I highlighted all data from A3:A510 and clicked Insert>Conditional Format and typed your formula with the exception of my VLookup which would be $C$517:$C$522 (for blue).A B C D 3 CB, CH 4 PL 5 CB, PL 6 ES 7 DC (VLookup data on same spreadsheet) 516 ML PC 517 CB DC 518 PL CH 519 KD RN 520 ES MB 521 CZ DW 522 GG

~~~~~

As for KK: technically he represents both ML & PC which would make him GreenAny other suggestions, or I am typing something incorrectly?

Thank your for your help!

~Rose

It's all working fine for me. I don't know about your machine, but my Conditional Formatting is on the Home ribbon, not the Insert ribbon (Excel 2010), but that shouldn't matter.

My steps are:

Select A2:A510

Click Home

Click Conditional Formatting

Click Manage Rules

Click New Rule

Click "Use a formula to determine which cells to format"

Put a formula in "Format values where this formula is true" field.

Click Format

Click Fill

Click a color

Click OK

Click OK

Click New Rule to add another rule or Apply to test it or OK to exitDon't take this the wrong way, but you area actually choosing your Fill colors of Blue and Yellow, right?

Based on your last post, I used these formulas for Blue, Yellow, Green and Green:

=NOT(ISNA(VLOOKUP(A2,$C$517:$C$522,1,0)))

=NOT(ISNA(VLOOKUP(A2,$D$516:$D$521,1,0)))

=AND(NOT(ISNA(VLOOKUP(LEFT(A2,2),$C$517:$C$522,1,0))),

NOT(ISNA(VLOOKUP(RIGHT(A2,2),$D$517:$D$521,1,0))))=AND(NOT(ISNA(VLOOKUP(RIGHT(A2,2),$C$517:$C$522,1,0))),

NOT(ISNA(VLOOKUP(LEFT(A2,2),$D$517:$D$521,1,0))))The

Applies Tofield reads: =$A$2:$A$510Try copying and pasting the formulas to make sure that they are right.

You can also try them in a cell and they should return either TRUE or FALSE. That's a trick for testing CF formula without going through the hassle of using the wizard. It they return TRUE in a cell they'll return TRUE in a CF

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History