Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    conditional formatting (2000 sr3)

    I want to format a range of cells based upon one cells value. For example, the range A1 to D4 is a data box that I consider active until I type the word "closed" in cell D4, then I want the entire range to become shaded.
    I could only find a way of shading cell D4 and not the whole range.
    any ideas?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formatting (2000 sr3)

    Select cells A14 then do the following:

    1- Select Conditional Formatting from the Format menu.

    2- In the drop down list in the dialog box select "Formula is".

    3- In the text box enter =$D$4="closed"

    4- Click on the Format button and select the formatting desired.

    5- Click OK until all dialog boxes are closed.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: conditional formatting (2000 sr3)

    Thanks, but now I am getting more creative in order to simplify the worksheet.
    This worksheet is full of duplicates of this A1 to A4 range. Each one is a different set I work on. Most are waiting to be used. The first thing I do is to type a name in the first cell, which is A1 in this sample.
    How would I format the range of cells on these two conditions: 1st, cell A1 must not be blank (that is, it must contain a name in order to differentiate from the other series' that have not been used yet and 2nd, A4 equals ($0.00). Cell A4 is a formula cell that, when the table is completed, returns the value of negative zero dollars. I want to format the cell range A1 to D4 to be shaded when the table is complete,i.e A4= ($0.00).
    Here's what I have:
    condition 1 = formula is......=$A$1<>""
    condition 1 = formula is..... =$A$4="($0.00)"
    but it doesn't work....help again please!!!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: conditional formatting (2000 sr3)

    formula is
    $A$4="($0.00)"
    compares A4 to the string "($0.00)"

    Do you want formula is:
    =and($A$4=0, $A$1<>"")

    I am not sure what you mean by "negative zero." Zero is zero. Do you mean something like:
    =and($A$4<0.001, $A$1<>"")

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: conditional formatting (2000 sr3)

    The reason I say negative zero is that that is the result on the screen "($0.00)", with the parenthesis. I attached a worksheet to illustrate the situation.
    This worksheet has several formulas to compute a balance on a loan after inputed dates.
    Once opened, you'll see I have three of these cell ranges copied. the first one, ranges A1 to D20 is how it looks before anything is done to it. the second one F1 to I20 is one where the loan was paid off and the bottom line-F20, equals "($0.00)"
    It is shaded because I used the last suggestion on conditional formatting. It only works when I do the ...."<0.001" part of the formula. The third cell range A22 to D41.
    Now the problem is that the third range too should be shaded, but it is not. The difference between the second and third is that on the third one I had only a partial payment on A28, and then the final payment on A34. (the second cell range pays the loan off on one payment) This brings the balance to "$0.00", instead of the "($0.00)" on the second range. This is why the conditional formatting doesn't work here.
    Now, I don't care about the negative zero aspect, but when I change the formulas to make it positive, it starts to mess everything else up.
    I know this is getting involved, but if you can think of any simpler way to do what I want to do: shade a cell range where there is a name entered and the balance of the loan is zero.
    I have a tendency to make things a lot harder than they could be.
    thanks,

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formatting (2000 sr3)

    If you increase the number of decimal placed displayed, you will see that cell F20 is actually -.00073733. That us why it shows as minus zero, it is actually a negative amount that rounds to zero when rounded to two decimal places. For your conditional formatting, you should probably use a test like:

    <pre>Round($F$20,2)=0
    </pre>

    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: conditional formatting (2000 sr3)

    last night i thought of the extended decimal idea...and your rounding idea sounds perfect...where do I put this "Round($F$20,2)=0"?
    I added it as another condition to the conditional formatting, but that didn't change it.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: conditional formatting (2000 sr3)

    Something like:

    =and(round($A$4,2)=0, $A$1<>"")

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: conditional formatting (2000 sr3)

    I still can't get that to work.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: conditional formatting (2000 sr3)

    Select A120
    format - cond format
    formula is:
    =and(round($A$20,2)=0, $A$1<>"")
    <format>
    Patterns(tab) choose the shading
    <ok><ok>

    Now if the value in A20 = 0 rounded to 2 decimals and A1 is not blank, then the A120 area is shaded

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: conditional formatting (2000 sr3)

    Thanks. I think I have it now

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: conditional formatting (2000 sr3)

    That's good.
    I think part of the problem was, that the range and cells we have been discussing keep changing.

    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
  •