Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Conditinal Formatting (Excel 2K and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    OK how do I make the Conditional Formatting take effect based on the cell value, and not be in effect when the cell is empty?

    For example, if the cell value is <> A then I want to have the font of the text bold, and I want the background of the cell to be red.

    But if the cell ie empty, I want it to show that its empty, not <> A and thus red background will show.

    I am trying to make a test result score sheet, so that if the correct answer is A it will be accepted, but when the answer is wrong, not A, then it will be flaged.

    Oh and is there a way to count these formatted cells, to determine the number of wrong answers? I think I will go to Mr. Peterson's web site for that, I remember it was posted that there is an example on his web site.

    Thanks a lot.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

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

    Re: Conditinal Formatting (Excel 2K and >)

    Use two conditions. In the first, use "Formula is" with the formula =$A$1="" (if the cell is A1) and set the format you want for an empty cell. Then in the second condition use "Cell value is", "Not equal to", and ="A".
    Legare Coleman

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditinal Formatting (Excel 2K and >)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Legare

    One question I always grappled with is:

    It seems that the conditional formats have an order, 1, then 2 and then 3.

    Is that correct?

    Thanks again

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  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: Conditinal Formatting (Excel 2K and >)

    Yes it is like the CASE in VB:
    If the 1st condition is true it does not matter what 2nd or 3rd is, it will format the 1st condition.
    If condition 1 is false and and condition 2 is true it will format the condition 2. It does not matter what cond 3 is
    Of cond 1 and cond 2 are false and cond 3 is true it will format the cond 3
    If all are false the explicit formatting is what is displayed.

    Note, that you can use ANDs and ORs in the formula to have multiple conditions in one conditional formula. Thus you could use the formula:

    =AND(NOT(ISBLANK(A1)),A1<>5)

    To highlight it when A1 is not 5 and is not blank. This allows you to use only one of the conditions instead of 2. You are limited to only 3 conditions and 4 formats.

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditinal Formatting (Excel 2K and >)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Steve

    I now can use this info while working with CFs.

    Thanks again.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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