Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    conditional formatting (office XP)

    hi, i think of an idea to highlight a cell when there is "0" in 'another' cell. i get used to this formula i get from someone, for example, =RIGHT($A15,20)="NET PROFIT/LOSS" using text , this still has error when it comes to 'numbers' because when the 'another' cell has 101, or 450, the selected cell will be highlighted as well. can someone shed some light here? i attach an example sample here. in manual i highlighted C11 with orange here when B12= 0. so, when i drag down the proper cell later, at C12, the cell will be highlighted when B13 is 0 and the C11 color will gone once B12 has numbers. hope my explanation useful. THANKS.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: conditional formatting (office XP)

    I'm not following your explanation in terms of the attachment. I regards only to the conditional formatting formula you mention, try

    =ISNUMBER(FIND("NET PROFIT/LOSS",A15))

    Perhaps you could attach a file that shows specifically what you want to do?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: conditional formatting (office XP)

    I'm not sure I understand either, but perhaps the following is what you're after:
    - Select C2:C18
    - Select Format | Conditional Formatting...
    - Select 'Formula is' in the first box.
    - Enter the following formula in the second box:

    =(B3=0)

    - Click the Format... button.
    - Activate the Pattern tab.
    - Select the color you want to use.
    - Click OK to close the Cell Properties dialog.
    - Click OK to close the Conditional Formatting dialog.

    The formuls =(B3=0) will be adjusted automatically for the other cells in the selection.

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formatting (office XP)

    Hi Hans, the =(B3=0) is correct for my worksheet but improvement is needed, where C12:C18 not highlighted too. only C11 highlighted. where at any column C row, the cell will be highlighted when the same row column B cell has any numbers and next column B row has 0. when the current row of B column cell has 0 and the next row also 0, the cell will not be highlighted. also, when the current row column B cell has numbers and next row column B has numbers, the cell in column C not highlighted. can you help me with this? THANKS.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: conditional formatting (office XP)

    Try this for your conditional formula:

    =AND(B3=0,B2<>0)
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formatting (office XP)

    Hi John, i got an idea of this when i saw your AND formula and makes the whole thing suits my homework, =IF(B2>0,B3=0). THANKS.

Posting Permissions

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