Changing cell color with either conditional formatting or macro

Closed
bealerusa Posts 5 Registration date Friday February 27, 2015 Status Member Last seen March 3, 2015 - Feb 27, 2015 at 11:56 AM
bealerusa Posts 5 Registration date Friday February 27, 2015 Status Member Last seen March 3, 2015 - Mar 3, 2015 at 10:57 AM
Excel 2003 or 2007, 3 preferred.
I have a column 'E' that contains either 2 or 4 digit text codes (MR, SP, BA SC, EU WO, etc), up to 50 unique codes. I need to highlight cells based on specific content (MR, EU WO, RG, etc., but not HM, HM CG, WO, etc.). Color of cell is not important, just not white. This is for quick visual identification so that I can perform other actions on that information while leaving the 'white' cells alone.
In 2003, using Conditional Formatting, I have had success with the following formula:
Cond. 1 = Formula is
=NOT(ISERR(SEARCH("MR",E1)))
Choose cell color RED
Problem arises because you are limited to 3 conditions in 2003. Question then is:
Can you modify the formula to search for more than 1 unique code at a time?
I have tried =NOT(ISERR(SEARCH("MR","RG",E1))) with no luck. Is my syntax wrong? Like the comma between the search items "MR" and "RG". What should it be? Or am I barking up the wrong tree? As may be potentially obvious, I know just enough to hurt myself with a spreadsheet... I use this on a daily basis and would really like to figure something out. My poor little mouse is tired out! This spreadsheet changes on a daily basis (daily order from 1 customer) and can contain upwards of 150 rows. The row info referenced by column 'E' needs to be separated for invoicing purposes, as well as order entry. Thanks, in advance, for any help someone may be able to offer.

Edit: So, OK. In 2007, it is possible to create a macro that starts 'X' number of conditional formatting instances that take care of what I need. I was hoping that something more elegant might be available. Also, something that would work in 2003.

Edit (2): In 2003, Conditional Format does not seem to allow me to run more than the 3 applications allowed. If, for instance, you make condition 1 through 3, then start a new instance of CF (like you would record a macro to manage multiple sorts) whatever changes you make to Condition 1 removes the formatting from the cell originally referenced in Cond. 1 and applies it to what could be considered Cond. '4'. Hopefully, I have not muddied the waters and someone is willing to shine a light my way. Thanks again!

1 response

RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Feb 27, 2015 at 04:11 PM
Make a list of all the valid values in say, column N:
LOOKUP
MR
SP
BA
.
.
SC
EU
etc.

Then in conditional formatting:
Choose "Use a formula..."
=MATCH($E2,$N$2:$N$51,0)

Where Column E contains the value you are matching against.
Apply the formatting/color to the range of data say:
=$A$2:$H$200
0
bealerusa Posts 5 Registration date Friday February 27, 2015 Status Member Last seen March 3, 2015
Mar 2, 2015 at 08:37 AM
Ray, Thank you for the quick response. I lost my well at home this weekend, so will be working some short days for the next few days. When I get the chance, I will take a look at your proposal and let you know. Thanks again! -Chad
0
bealerusa Posts 5 Registration date Friday February 27, 2015 Status Member Last seen March 3, 2015
Mar 2, 2015 at 11:22 AM
Is there a simple way to 'upload' a sample spreadsheet so that you may look at it? Leaving for the day, but will check back in the morning. Thanks! -Chad
0
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Mar 2, 2015 at 10:54 PM
you can upload to www.speedyshare.com
and post the link to it here.

Curious though, did you try my suggestion?
If so, did it work?

Can you give more details on what you think needs to be done once you upload the file.
0
bealerusa Posts 5 Registration date Friday February 27, 2015 Status Member Last seen March 3, 2015 > RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016
Mar 3, 2015 at 09:34 AM
Ray, gave it a quick go yesterday. No Joy. I am going to look at it some more today. I got my well fixed so should have some more time in the office. It is very possible that I did something wrong in setting up the conditional formatting section. I will double check my formula to make sure I didn't 'fat-finger' it. Stay tuned and Thanks! -C-
0
bealerusa Posts 5 Registration date Friday February 27, 2015 Status Member Last seen March 3, 2015
Mar 3, 2015 at 10:57 AM
Ray, hopefully this gets you where you need to go...

http://speedy.sh/QVBYp/raw-file-for-conditional-format-3.3.15.xls

I created a minimal Lookup list in N as you suggested. I then applied a Conditional Format to E. At this point, it appears to only be picking up the value in N6 and ignoring N2-N5. It formats the requested cells as expected. However, if you change the value in N2 to "YB CG" it then formats the entire rows of 24,26,29,35,45. And, if you change the value in the CF formula reference to something else (say, E3) the results are unexpected.

Don't really know where I took a wrong turn. Thanks for all your help! -C-
0