Conditional Formatting

[smpaz7467]

I have a question about conditional formatting. I am a civil engineer, I use excel to go through each lot to see what house plans work and dont work. I want to color code my different codes that I use to make it easier to see what works and doesnt work. The problem I am running in to is that I can only formatt 3 codes per cell. I need to be able to formatt 10 or so different codes, but I am only using 2 colors. Ex. "LT" is Blue, "NA", "NW" and a couple more codes in Red. Anyone have any idea how to do this?

 

[erik.van.geit]

Hi, smpaz7467
Welcome to the Board !!!!!

this is a question we see rather often
you can do a search for conditional format multiple criteria
example
http://www.mrexcel.com/board2/viewtopic.php?t=133824
http://www.mrexcel.com/board2/viewtopic.php?t=165325
see the Select Case solution of SidneyGeek

kind regards,
Erik

 

[smpaz7467]

Thanks Erik. Good to be here

Tried using different codes but couldnt get anything to work.

This is what I need it to do.

Change-LT to blue
Change-RT to green
Change-NA,NCR,NCL,ND,NW,NDW,FX,NFX,NLF,NRF- these are all individual codes that may be entered in, to red

They are in certain columns and rows, but i would like the entire spreadsheet to change if it finds these values. Also I would like it to change them if the are either uppercase or lowercase. Anyone have any ideas???

 

[Scott Huish]

Is there only one code per cell? If so, this should be doable with regular Conditional Formatting, what column are these values in and what range do you wish to highlight?

 

[erik.van.geit]

but couldnt get anything to work.

if you want help you will need to be more specific
what did you try (pot some code) and what was the problem

we can give you a fish, so you can eat, but I'm in the mood to help you how to fish

TIPS:
1.
first forget about your own spreadsheet and try out the code literally as in the example
then modify step-by-step to get it do what you want

2.
most of the links about CF (Conditional Format) use "event"code: when sheet changes the code is triggered
therefore you need to pust the code in the sheetmodule
did you do that ?
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE

interesting link
http://www.cpearson.com/excel/events.htm

best regards,
Erik

 

[erik.van.geit]

just now saw you told us

but I am only using 2 colors

I should have replied like Hotpepper
sorry for that
BUT

Change-LT to blue
Change-RT to green
Change-NA,NCR,NCL,ND,NW,NDW,FX,NFX,NLF,NRF- these are all individual codes that may be entered in, to red

here you have three colors !!

one more and you WILL need code
(unless you want to wait till next Excell-version)

I'll try to repair my mistake with a long post

let's put the codes in a sheet like this
   A    B     C   
 1 blue green red 
 2 LT   RT    NA  
 3            NCR 
 4            NCL 
 5            ND  
 6            NW  
 7            NDW 
 8            FX  
 9            NFX 
10            NLF 
11            NRF 

test
[Table-It] version 06 by Erik Van Geit

let's put conditional format in cell E1
1ST CONDITION (2nd is same method)
I suppose you know how to check for one value
"value" =$A$2
or using "formula"
=E1=$A$2
this is not casesensitive when used within CF
3RD condition
=MATCH(E1,$A$1:$A$5,0)>0

remark:
you could use dynamic named ranges for the three lists
http://www.datapigtechnologies.com/flashfiles/dynamicranges.html
If you are on Excel 2003, convert the data area into a list by means of Data|List|Create List. You'll never miss anything when you do a sort.
now you can refer

best regards,
Erik

 

[smpaz7467]

I am new to this type of coding. I have basic excel skills. I copied a couple of different types of code I found while searching but nothing really match what I need. So I guess I need to get the basics down and go from there.

 

[erik.van.geit]

I am new to this type of coding. I have basic excel skills. I copied a couple of different types of code I found while searching but nothing really match what I need. So I guess I need to get the basics down and go from there.

you probably missed my last reply with the good news

 

[smpaz7467]

There are going to be 12 codes per cell. I need LT and all the codes in red to be in Column C2-C3500. And RT and all the coded in red in Column D2-D3500. I am using Ecel 97, but I am setting this up as a template for work, with many different clients using this so it will have to work with all different versions.

 

[Scott Huish]

Post deleted.