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?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Conditional Formatting
- Thread starter smpaz7467
- Start date
erik.van.geit
MrExcel MVP
- Joined
- Feb 1, 2003
- Messages
- 17,832
wait a moment
we talked about conditional format
now it seems to me you want to move the data ...
that's another story !!
let's first finish with the CF
did you see my extensive reply with information ... ?
then
STEP 1 reproduce the situation litterally
STEP 2 experiment doing some little modifications
STEP 3 apply to your situation
EDIT: if you can make it work with XL97, all other versions will run it
Upvote
0
Excel Facts
HotPepper, I only have one code per cell. But there is the possibilty that any one of the 12 codes will be in a cell. That is why i want to color code them. If you tell me how to post an a copy of my spreadsheet I can explain what I need alot easier.
Upvote
0
erik.van.geit
MrExcel MVP
- Joined
- Feb 1, 2003
- Messages
- 17,832
colo's HTMLmaker (see link bottom page) or Table-It (see my signature)
Upvote
0
A B C D E F
1 Lot Number Plan Name Left Driveway Right Driveway Front Setback Rear Setback
2 1 5 LT NCR 20 20
3 1 6 LT NCR 20 20
4 2 5 LT NA 20 20
5 2 6 NCL RT 20 20
6 3 5 LT ND 20 20
7 3 6 NW RT 20 20
8 4 5 LT NDW 20 20
9 4 6 FX RT 20 20
10 5 5 LT NFX 20 20
11 5 6 NLF RT 20 20
12 6 5 LT NRF 20 20
13
14
15 LIST OF CODES
16 LT BLUE
17 RT GREEN
18 NA RED
19 NCR RED
20 NCL RED
21 ND RED
22 NW RED
23 NDW RED
24 FX RED
25 NFX RED
26 NLF RED
27 NRL RED
Sheet 1
[Table-It] version 06 by Erik Van Geit
1 Lot Number Plan Name Left Driveway Right Driveway Front Setback Rear Setback
2 1 5 LT NCR 20 20
3 1 6 LT NCR 20 20
4 2 5 LT NA 20 20
5 2 6 NCL RT 20 20
6 3 5 LT ND 20 20
7 3 6 NW RT 20 20
8 4 5 LT NDW 20 20
9 4 6 FX RT 20 20
10 5 5 LT NFX 20 20
11 5 6 NLF RT 20 20
12 6 5 LT NRF 20 20
13
14
15 LIST OF CODES
16 LT BLUE
17 RT GREEN
18 NA RED
19 NCR RED
20 NCL RED
21 ND RED
22 NW RED
23 NDW RED
24 FX RED
25 NFX RED
26 NLF RED
27 NRL RED
Sheet 1
[Table-It] version 06 by Erik Van Geit
Upvote
0
Now, any of the codes can display in column C or D, BUT only 1 will show. What I need to do is make a code so that each message that will display will be the proper color per cell. Again only one message per cell, but it could be any of the messages. Do you guys understand what i am saying?
Upvote
0
erik.van.geit
MrExcel MVP
- Joined
- Feb 1, 2003
- Messages
- 17,832
yes, since I replied with
and I'm sure Hotpepper does understand too
it's difficult to work together when not recieving feedback
did you try my suggestions ?
anyway you'll need some practicing to work with CF
in the mean time I'll see if your layout is OK to implement (I think so)
Upvote
0
erik.van.geit
MrExcel MVP
- Joined
- Feb 1, 2003
- Messages
- 17,832
OK,
it was easier then I thought at first glance
the formulas are more "expensive" then using my system
you can still use a "dynamic named range" if you want
to illustrate the formulasystem add some formules to your sheet
the output will be "BLUE", "RED" or "GREEN"
now let's apply this to CF
select C2:D12
for each condition you will need to compare to a different string
=INDEX($B$16:$B$27,MATCH(C2,$A$16:$A$27,0))="BLUE"
=INDEX($B$16:$B$27,MATCH(C2,$A$16:$A$27,0))="GREEN"
=INDEX($B$16:$B$27,MATCH(C2,$A$16:$A$27,0))="RED"
as said replacing A16:A27 by a dynamic range would be nice
it was easier then I thought at first glance
the formulas are more "expensive" then using my system
you can still use a "dynamic named range" if you want
to illustrate the formulasystem add some formules to your sheet
Code:
RANGE FORMULA (1st cell)
H2:I12 =INDEX($B$16:$B$27,MATCH(C2,$A$16:$A$27,0))
[Table-It] version 06 by Erik Van Geitnow let's apply this to CF
select C2:D12
for each condition you will need to compare to a different string
=INDEX($B$16:$B$27,MATCH(C2,$A$16:$A$27,0))="BLUE"
=INDEX($B$16:$B$27,MATCH(C2,$A$16:$A$27,0))="GREEN"
=INDEX($B$16:$B$27,MATCH(C2,$A$16:$A$27,0))="RED"
as said replacing A16:A27 by a dynamic range would be nice
Upvote
0
It is saying there is a circular reference. And I only included the list of codes as a reference. Is there a way to imped these codes in that view code thing, so they do not display. I will post another view of the spread sheet for you to view as one of my clients would.
Upvote
0
A B C D E F
1 Lot Number Plan Name Left Driveway Right Driveway Front Setback Rear Setback
2 1 5 NCR 20 20
3 1 6 LT NCR 20 20
4 2 5 NA 20 20
5 2 6 NCL RT 20 20
6 3 5 LT ND 20 20
7 3 6 NW RT 20 20
8 4 5 LT NDW 20 20
9 4 6 FX RT 20 20
10 5 5 LT NFX 20 20
11 5 6 NLF RT 20 20
12 6 5 LT NRF 20 20
Sheet 1
[Table-It] version 06 by Erik Van Geit
1 Lot Number Plan Name Left Driveway Right Driveway Front Setback Rear Setback
2 1 5 NCR 20 20
3 1 6 LT NCR 20 20
4 2 5 NA 20 20
5 2 6 NCL RT 20 20
6 3 5 LT ND 20 20
7 3 6 NW RT 20 20
8 4 5 LT NDW 20 20
9 4 6 FX RT 20 20
10 5 5 LT NFX 20 20
11 5 6 NLF RT 20 20
12 6 5 LT NRF 20 20
Sheet 1
[Table-It] version 06 by Erik Van Geit
Upvote
0
That is what my clients are going to recieve from me. I am not going to put the list of codes in the file, unless I have to. Also this is a small example of what I am working with. I need this to be able to work from cells c2-c3500. and the same for cells D2-D3500. Sorry if I am being a pain in the neck, I just want to figure this out and I appreciate the help!!
Upvote
0
Similar threads
