Changing cell color with either conditional formatting or macro

[Closed]
Report
Posts
5
Registration date
Friday February 27, 2015
Status
Member
Last seen
March 3, 2015
-
Posts
5
Registration date
Friday February 27, 2015
Status
Member
Last seen
March 3, 2015
-
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 reply

Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
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
Posts
5
Registration date
Friday February 27, 2015
Status
Member
Last seen
March 3, 2015

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
Posts
5
Registration date
Friday February 27, 2015
Status
Member
Last seen
March 3, 2015

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
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
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.
Posts
5
Registration date
Friday February 27, 2015
Status
Member
Last seen
March 3, 2015
>
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016

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-
Posts
5
Registration date
Friday February 27, 2015
Status
Member
Last seen
March 3, 2015

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-