Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Kenton, Delaware, USA
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    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,

Posting Permissions

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