# 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 ACB, CHPLCB, PLCB, CHESCHDC, CHDC, KKCHKDCB, CHESIdeally, 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

#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 D3 CB, CH4 PL5 CB, PL6 ES7 DC~~~~(VLookup data on same spreadsheet)516 ML PC517 CB DC518 PL CH519 KD RN520 ES MB521 CZ DW522 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

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 GreenAny 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:A510Click HomeClick Conditional FormattingClick Manage RulesClick New RuleClick "Use a formula to determine which cells to format"Put a formula in "Format values where this formula is true" field.Click FormatClick FillClick a colorClick OKClick OKClick 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 To field 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 CFPosting Tip: Before posting Data or VBA Code, read this How-To.

Report • 