Excel-Conditional Formating OR Formulas

Microsoft Microsoft excel 2007 full vers...
December 7, 2010 at 11:32:43
Specs: Windows 7
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


See More: Excel-Conditional Formating OR Formulas

Report •


#1
December 7, 2010 at 17:39:36
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 this How-To.


Report •

#2
December 7, 2010 at 18:16:19
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	GG	

CF 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, CH and CH, 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 this How-To.


Report •

#3
December 9, 2010 at 11:27:29
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 Green

Any other suggestions, or I am typing something incorrectly?

Thank your for your help!

~Rose


Report •

Related Solutions

#4
December 9, 2010 at 12:34:04
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 this How-To.


Report •

#5
December 9, 2010 at 13:18:22
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 Green

Any other suggestions, or I am typing something incorrectly?

Thank your for your help!

~Rose


Report •

#6
December 9, 2010 at 17:41:52
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 exit

Don'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 To field reads: =$A$2:$A$510

Try 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 this How-To.


Report •

Ask Question