Conditional Formatting (More Than 4 Conditions) - Using Different

[zorrow99]

I converted my Office 2003 to Office 2007 thinking that I can accomplish this
task in Excel 2007. My question is the following example:
I have data in Cells from B1 through B15 and C1 through C15. Now trying to say
1. if A1 is selected with (x) - highlight and bold font from B3/C3 through
B5/C5
2. if A2 is selected with (x) - highlight and bold font from B8/C9 through
B15/C15
3. if A3 is selected with (x) - highlight and bold font from B5/C5 through
B10/C10...

Yes there are some will overlap eachother and this is were I am having
problem with CF. I tried to use AND logic, but the CF indicated that it is
not possible to innerjoin with AND logic in CF. I am not Excel person and
would like to know if there is anyone who will tell me where to go and how to
apply any formulas. I do not know how to use Macro nor do I know how to use
the VB. But I need help Please...

Thanks

 

[JoAnn Paules]

I had a sheet that needed conditional formats based on values in other
cells, which were formulas based on other cells. I don't do Excel macros or
VBA either. In my case, my husband helped me write an IF statement that did
what I needed. Really hairy, scary formula!

I also had a spreadsheet where I needed four conditional formats based on
the contents of that specific cell. I use Excel 2003 so I was limited to
three. I decided that one of the formats was my default and used CF for the
other three. It works for what I need

I would probably suggest you post your question over in one of the
Excel-specific newsgroups and see if someone can't help you. It may take
some creative thinking.

 

[zorrow99]

Thanks JoAnn. I have tried to ask different groups and no luck... I tried
using Excel 2003 and did it like you said, 1 default and I am still limitted
to 3, therefore using 1 leaves me with 2 more... Can you give me trick how
you used 1 default that leaves you with 3 still? If you can give me this
insight, I believe I can accomplish my task.

I had a sheet that needed conditional formats based on values in other
cells, which were formulas based on other cells. I don't do Excel macros or
VBA either. In my case, my husband helped me write an IF statement that did
what I needed. Really hairy, scary formula!

I also had a spreadsheet where I needed four conditional formats based on
the contents of that specific cell. I use Excel 2003 so I was limited to
three. I decided that one of the formats was my default and used CF for the
other three. It works for what I need

I would probably suggest you post your question over in one of the
Excel-specific newsgroups and see if someone can't help you. It may take
some creative thinking.


--

JoAnn Paules
MVP Microsoft [Publisher]
Tech Editor for "Microsoft Publisher 2007 For Dummies"

I converted my Office 2003 to Office 2007 thinking that I can accomplish
this
task in Excel 2007. My question is the following example:
I have data in Cells from B1 through B15 and C1 through C15. Now trying to
say
1. if A1 is selected with (x) - highlight and bold font from B3/C3 through
B5/C5
2. if A2 is selected with (x) - highlight and bold font from B8/C9 through
B15/C15
3. if A3 is selected with (x) - highlight and bold font from B5/C5 through
B10/C10...

Yes there are some will overlap eachother and this is were I am having
problem with CF. I tried to use AND logic, but the CF indicated that it is
not possible to innerjoin with AND logic in CF. I am not Excel person and
would like to know if there is anyone who will tell me where to go and how
to
apply any formulas. I do not know how to use Macro nor do I know how to
use
the VB. But I need help Please...

Thanks

 

[ohnonotnow!]

zorrow--i

i'm no expert but.... don't see what the problem here is, which probably
means i misunderstand what you are trying to do
but let's see....

excel 2007 does not have the 3 rule restriction. the number of possible
rules is virtually limitless. which makes this much easier than older
versions.

the only possible problem is for those areas of the column that would have
conditional formatting applied by more than one condition.
simply, the higher condition overrules lower conditions. so --

*select the cells you want conditionally formatted
*open the Manage Rules... from Conditional Formatting ribbon icon
*click New Rule...button
*click Use a formula to dertermine which celss to format
*enter in the format values where this formual is true: box
=$A$2="X"
*click Format...button and go about the formatting you want
*OK, OK, APPLY

DO THIS 2 MORE TIMES FOR A2 AND A3 (first select the cells you want
conditionally formatted for the particular condition you want
*click New Rule... etc
*select the full range you are conditionally formatting and then change the
order of precedence by using the up down arrows at the top of the rules
Manager box

this means that condition 1 (for A1) will override the condition for A2 and
A2 will override the condition formatting for A3 WHERE THEY CONFLICT
(OVERLAP).

if this isn't what you want, i..e. you want different formatting for those
areas where they do overlap, simply create another condition for each of
those overlapping cell groups ; select those cells and do the same procedure.

OR use the AND( formula. for example:
and you can use AND( formulas =AND($A$1="X",$A$2="X")
select first the cells you want this applied to, and remember how the order
of the formulas in the Conditional Formatting Rules Manager box.

is this at all helpful?

//onnn!

 

[zorrow99]

I believe I missed a little extra explaination as to what I really wanted
from the CF... Sorry for the confusion:
I meant to say that if I want to select A1 through C1 and jumping from row 1
to row 5 and jump to row 10 (A5 through C5 and A10 through C10)...
I was able to run multiple CF with your suggestion and I liked it - Thank
you for that tip... But if you can help me with this situation I would be
more grateful.
The error message I get when I try to do CF with what I want to accomplish -
MS Office Excel prompts me with an error message "You may not use unions,
intersections, or array constants for Conditional Formatting criteria"...

Any suggestions on this ~

Thank you very much for your assistance friend

 

[ohnonotnow!]

i've been away. sorry it didn't work. why not post here exactly what you
are using in the CF boxes that prompts the error message. perhaps it will be
more understandable, cause i'm really not sure what you want.
(OR, if you've gotten the sollution by now, post that, if you would....)


onnn!

 

[zorrow99]

Good afternoon everyone... I have also been away and I am back. I have got it
working and I used IF statement.
=IF($H$44:$K$44="x",1,0)
and second part
=$H$44:$K$44,$H$47:$K$55
again the numbers repersent the cell (column and row)

thanks,

 

[zorrow99]

I forgot to mention - this works for Office 2007. It is not compatible with
Office 2003. I tried to save it for previous version user and it gave me
error message.

thanks,