# Thread: Conditional Formating (2000)

1. ## Conditional Formating (2000)

I need a piece of code to put in the formula box in the conditional formatting wizard. What I need it to do is look for a chain of blocks containing some value and coloring them so that they appear as a group. . Since the value is a count number my first condition is "any cell with a value equal to or greater than 1".That was the easy part, but i don't know how to set the second condition. The second condition would be "the cell above it OR the cell below it also has a value equal to or greater than 1" when the two conditions are met then color it red. I would also like to do the same thing horizontally. Can anyone help me with this?

2. ## Re: Conditional Formating (2000)

Not entirely sure what you want. You must remember you can only have 3 conditions and they are mutually exclusive:
If condition 1) is true (=B2 = 1) then it will format the cell this color and not look at any of the other conditions
if condition 1 is false and condtion 2 is true [=or(b1=1,b3=1)] , it will color it this format and not look at cond 3
if condition 1 is false and condtion 2 false and cond 3 is true [=or(a1=1,c1=1)] , it will color it this format.
if all are false it uses the explicit formatting you have set

so you can have 3 colors. If you set them all to red, it will be red, if any of the conditions of the above are true. If you only want it red when all are true:
=and (B2 = 1,or(b1=1,b3=1),or(a1=1,c1=1))
is the formula you want to use.

You could set =1 to blue color, <>1 but all border (row and col) =1 to red color and if =1 and all border = 1 to green color. In this case cond 1 must be the = 1 and all border (green color) or else it will never be found true, since to be tested 1 and 2 must be false, but for 3 to be true both 1 and 2 must be false so the cond must be rearranged:
Cond 1 =and (B2=1, b1=1,b3=1, a1=1,c1=1))
Cond 2 = B2=1
Cond 3 =and (b1=1,b3=1, a1=1,c1=1))

Hope this helps,
Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•