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]

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.

please be clear to which message ou are replying

if you do exactly as described there is NO circular reference
I almost never reply without setting up the same sheet as the original poster.
in fact Conditional Format cannot have circular references: so I suspect you were entering the formulas for CF as normal formulas within the cells

so please retry
if you still have problems be specific
what did you do and what was the error ?
take your time, I'm sleeping now

 

[smpaz7467]

The range formula, do I add this into the cell as I would if i was adding up 2 columns. I have tried to put this code in but it displays the code and doesnt disappear like a code normally would.

 

[Scott Huish]

If you don't want a table then perhaps:

Select Columns C and D.
Click on Format, then Conditional Formatting
Under Condition 1
Cell Value is equal to LT
Click on Format, click on Patterns, select a Blue, Click OK

Click on Add
Under Condition 2
Cell Value is equal to RT
Click on Format, click on Patterns, select a Green, Click OK

Click on Add
Under Condition 3
Change Cell Value is to Formula Is:
=OR(C1="NA",C1="NCR",C1="NCL",C1="ND",C1="NW",C1="NDW",C1="FX",C1="NFX",C1="NLF",C1="NRF")
Click on Patterns, Select a Red, Click OK
Click OK

 

[erik.van.geit]

The range formula, do I add this into the cell as I would if i was adding up 2 columns. I have tried to put this code in but it displays the code and doesnt disappear like a code normally would.

as suggested before: to get good help
1. tell us what message you are replying to and provide more feedback
2. what you are doing exactly ?

else we are swimming in confusion

 

[smpaz7467]

If you don't want a table then perhaps:

Select Columns C and D.
Click on Format, then Conditional Formatting
Under Condition 1
Cell Value is equal to LT
Click on Format, click on Patterns, select a Blue, Click OK

Click on Add
Under Condition 2
Cell Value is equal to RT
Click on Format, click on Patterns, select a Green, Click OK

Click on Add
Under Condition 3
Change Cell Value is to Formula Is:
=OR(C1="NA",C1="NCR",C1="NCL",C1="ND",C1="NW",C1="NDW",C1="FX",C1="NFX",C1="NLF",C1="NRF")
Click on Patterns, Select a Red, Click OK
Click OK

HOTPEPPER

I have done what you suggested which work. But when it changes the color to red it doesnt always work. Ex. if I have RT in cell D12 and then NCR in D13, NCR stays black. Any ideas on how to fix this? Other than that It works great

 

[smpaz7467]

HOTPEPPER, I GOT IT. I JUST CHANGED C1 TO C2 AND SO ON AND IT WORKED. THANKS FOR ALL THE HELP GUYS IT IS GREAT APPRECIATED. DO EITHER OF YOU KNOW HOW DIFFICULT IT WOULD BE TO GET EXCEL TO TAKE ALL THE DATA THAT I HAVE AND MAKE A SEPERATE SHEET FOR EACH LOT?

 

[Scott Huish]

HOTPEPPER, I GOT IT. I JUST CHANGED C1 TO C2 AND SO ON AND IT WORKED.

You shouldn't have had to do that though if you had selected both complete columns first.