Excel Help

[Big Country]

I think there may be a way to solve this using conditional formatting, but I havent been able to figure it out.

 

Basically I have a matrix that is filled out with text values.

 

Now, depending on what this text value is, I want to color code the cell. Problem is, I am dealing with a fairly large number of different text values (over 30), but I only need to use 3 colors (each text value is grouped into one of three groups)

 

I suppose I could write out a 30+ condition conditional formatting argument, but that is very time consuming.

 

Is there a way to state that if the cell is equal to this one of this list of values, shade this color, if not, check this list of values and shade that color, and if not part of that list check the final list and shade that color?

 

Each text value does relate to a number value, so I could set up ranges based on the number values, however, there is no way to conditionally format a cell based on the value in another cell... at least not that I have been able to figure out.

 

Does anyone have any ideasout there of a way for me to figure this out without having to manually go through each cell and shade based on the value?

[Thews40]

I think there may be a way to solve this using conditional formatting, but I havent been able to figure it out.

 

Basically I have a matrix that is filled out with text values.

 

Now, depending on what this text value is, I want to color code the cell. Problem is, I am dealing with a fairly large number of different text values (over 30), but I only need to use 3 colors (each text value is grouped into one of three groups)

 

I suppose I could write out a 30+ condition conditional formatting argument, but that is very time consuming.

 

Is there a way to state that if the cell is equal to this one of this list of values, shade this color, if not, check this list of values and shade that color, and if not part of that list check the final list and shade that color?

 

Each text value does relate to a number value, so I could set up ranges based on the number values, however, there is no way to conditionally format a cell based on the value in another cell... at least not that I have been able to figure out.

 

Does anyone have any ideasout there of a way for me to figure this out without having to manually go through each cell and shade based on the value?

 

I don't think you can define more that three in conditional formats. What I've done, is set up the adjacent column to equal a number depending on the data. Not sure what your text is, but if it was defined to be one of three numbers with an if statement, you could then use conditional formatting depending on the answer. Lookup tables can be used here too, but they are a pain to define.

[Sam]

I believe you could do it with code using an If - Else statement.

Edited May 21, 2007 by Sam

[SayItAintSoJoe]

Set up the range numbers in hidden columns or a hidden sheet and use:

 

conditional formatting -> Cell Value is between

 

and select the ranges from the hidden columns (sheet)

[Kid Cid]

I believe you could do it with code using an If - Else statement.

 

Very simple to do with a Case statement.

[Sam]

Very simple to do with a Case statement.

 

 

True. Either would work.

[Big Country]

Not so good with code, so all I did was simply add rows to my matrix for the numerical values associated with my text values, VLOOKUPed them into my matrix from the list of said values and conditionally formatted the numerical cells, then just took a minute manually filling the text cells to match the corresponding numerical cell in shading. Not the perfect solution, but it'll work for my purposes.

[Sam]

Not so good with code, so all I did was simply add rows to my matrix for the numerical values associated with my text values, VLOOKUPed them into my matrix from the list of said values and conditionally formatted the numerical cells, then just took a minute manually filling the text cells to match the corresponding numerical cell in shading. Not the perfect solution, but it'll work for my purposes.

 

 

As long as it works.