locked
About Font colour of conditional formatting in Excel RRS feed

  • 問題

  • This is the data file that I have record in Excel:

     

    25 Aug 08 21104.79 $556.15 32605.90 43606.32 24908.90 11558.34
    26 Aug 08 21056.66 $479.58 32564.50 43426.06 25053.63 11499.63
    27 Aug 08 21464.72 $609.88 33142.76 43315.65 25601.79 11768.46
    28 Aug 08 20972.29 $667.27 32703.48 43145.18 25524.53 11290.34
    29 Aug 08 21261.89 $634.44 33365.66 42552.50 26256.61 11364.19

     

    Please help to set the formula~

    I tried to set in the Format/Conditional Formating, it doesn't help.

     

    Each collum,

    if the value of one cell is bigger than that of previous/above cell, font colour change to Green;

    and,

    if the value of one cell is smaller than that of previous/above cell, font colour change to Red.

     

    I can do it one by one, but it's meaningless and I can simply click the font colour icon.

     

    Is it possible to set a templete and apply to the whole spreadsheet?

     

    Many thx

    2008年10月3日 3:29

解答

  • Hi if I set the rule to:

    Rule: Cell Value >= B2, Format: Color Green, Applies To: = $ C $ 2 : $ G $ 5

    Rule: Cell Value < B2, Format: Color Red, Applies To: = $ C $ 2 : $ G $ 5

     

    everything work fine, please check it.

    2008年10月7日 3:01

所有回覆

  • Select Format > Conditional Format, use that to check the behavior and config the color.

     

    Check if it's work.

     

    It should under Office Setting and I'd suggest to move this thread to Office System

    2008年10月3日 6:32
  • Thank you, I have select like this:

     

    Condition 1:

    Cell Value is greater than or equal to = $8$68

    Condition 2:

    Cell Value is less than = $8$68

     

    The cell is changed (Actually I tried before, but I don't know how to apply to the whole spreadsheet, simply few steps instead of do it one by one)

     

    Million thanks if you can help~~~

     

    22 Sep 08 19632.20 $916.35 31256.96 41143.93 22295.80 10073.74
    23 Sep 08 18872.85 $643.69 30707.96 40920.09 21286.42 9795.87
    24 Sep 08 18961.99 $590.06 30813.14 41101.29 21110.10 9888.45
    25 Sep 08 18934.43 $522.36 30923.65 41324.34 21085.56 9805.84
    26 Sep 08 18720.57 $527.16 30840.20 41204.61 20668.63 9553.22

    2008年10月3日 14:55
  • Hi Angus,

    Have you tried to Select ALL the cells that you want to apply for the "Conditional Formatting" function? E.g. All the cells which contains Numbers.

    In your case you may want to select All, Except the Header Column E.g. The Date.
    Here it is an example URL:
    http://www.contextures.com/xlCondFormat02.html

    Please forgive me if I mis-understood your issue.

    Hope this can help.


    2008年10月3日 17:03
  • Really appreciate your help~

     

    But I have tried before.

     

    It does work for comparing with a constant value, but I can't work it out for comparing with "a value just above itself, respectively",

     

    ie, A2 compared to A1, A3 compared to A2.........etc

     

    help~~~~

     

    2008年10月6日 14:59
  •  

    Hi Angus,

     

    I understood now.


    What you need to do is to REMOVE the Absolute Reference

     

    E.g. instead of using $B$68

    You use B68 on the first cell you want to apply the "Conditional Format"

    1. Once you done that you can just click on that Cell and Copy it
    2. Then you select a range of Cells that you want to apply the "Conditional Format"
    3. Go to "Edit" -> "Paste Special..."
    4. Then click on "Formats"
    5. And Click "OK"

    Hopefully it should work. (I have tested it [again, if i did not mis-understood the complication])

     

    Please let me know if you wants more information, I can send you the sample Spreadsheet that i did.

     

    Hope this can help.

     

     

    2008年10月6日 15:20
  • Almost works~~

     

    When I paste the format, it keeps on compared to B67 with every cell.

     

    When I set comditional format on two consecutive cells, it still can't format automatically with just previous cell.

     

    2008年10月6日 16:49
  • Hi Angus

    Which version of MS Office are you using? My one is Office 2003. Would you mind to send us a sample of your spreadsheet. With the formula what you use for the Conditional Format.

    so that we can have a try.


    2008年10月6日 17:48
  • Hi Angus,

     

    I can do the same result with ChiYau, please ensure your rule has remove all "$"

     

    Jacky

     

    2008年10月7日 1:48
  • Same, I used 2003 version.

     

    Already sent the files to you all~

     

    To Chi Yau, can't find your email~~

     

    Thx~

     

    2008年10月7日 2:11
  • Hi if I set the rule to:

    Rule: Cell Value >= B2, Format: Color Green, Applies To: = $ C $ 2 : $ G $ 5

    Rule: Cell Value < B2, Format: Color Red, Applies To: = $ C $ 2 : $ G $ 5

     

    everything work fine, please check it.

    2008年10月7日 3:01
  • Hi Angus,

    Sorry, I have forgotten to put my email to my profile, I have updated it now.
    I guess Jacky has already email you the solution he did.
    Please check your email.
    2008年10月7日 7:26