Conditional Formatting

smpaz7467

Board Regular
Joined
Sep 18, 2006
Messages
77
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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

smpaz7467

Board Regular
Joined
Sep 18, 2006
Messages
77
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
:oops:
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

Board Regular
Joined
Sep 18, 2006
Messages
77

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Sep 18, 2006
Messages
77
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.
 

Forum statistics

Threads
1,137,335
Messages
5,680,887
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top