+ Reply to Thread
Results 1 to 10 of 10

Conditional Format cells if greater than or less than zero

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    Dade City, FL
    MS-Off Ver
    Excel 2007
    Posts
    43

    Conditional Format cells if greater than or less than zero

    The purpose of this spread sheet is to calculate the revenue of a campground.

    In cell F36 = (calculated revenue)
    F37 = (actual revenue - counted cash & checks)
    F38 = (the difference, positive or negative (F36 - F37)
    E38 = "short"
    D38 = "over"
    C38 = "Discrepancy"

    Now the question:

    I would like cell C38, cell D38 and F38 to be colored black if F38 is positive or zero.
    I would like cell C38, cell E38 and F38 to be colored red if F38 is negative.

    Is this over the moon or is it possible?

    Thanks,

    Mel
    Last edited by jeffreybrown; 02-13-2013 at 08:08 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,056

    Re: Excel General

    Administrative Note:
    • We would love to continue to help you with your query, but first, before we can proceed…
    • Please see Forum Rule #1 about a proper thread title and adjust accordingly...
    • Changed for you this time, but please pick an appropriate title next time…Thanks.

    Hi Mel,

    Try some conditional formatting...

    Conditional Formatting
    • Highlight applicable range >> $C$38:$D$38,$F$38
    • Home Tab >> Styles >> Conditional Formatting
    • New Rule >> Use a formula to determine which cells to format
    • Edit the Rule Description: Format values where this formula is true: =AND(ISNUMBER($F$38),$F$38>=0)
    • Format… [Number, Font, Border, Fill] >> Black
    • OK >> OK

    Add a new rule, but this time the formula use:
    =AND(ISNUMBER($F$38),$F$38<0) >> and format Red
    Last edited by jeffreybrown; 02-13-2013 at 08:11 PM.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    Dade City, FL
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Conditional Format cells if greater than or less than zero

    Jeff: Thanks for your answer.

    I have such a low level of experience with Excel that I don't have any understanding of your answer. Would you please, given the info in the original post show me what rules to put in what cells. I have learned very much in the last month but not enough to handle this.

    Thanks again.

    Mel B The B is for Brown

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,056

    Re: Conditional Format cells if greater than or less than zero

    It would help if I knew what part of the directions in post #2 you get stuck with?

  5. #5
    Registered User
    Join Date
    02-06-2013
    Location
    Dade City, FL
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Conditional Format cells if greater than or less than zero

    I think it is pretty obvious that I do not have any grasp of conditional formating. Simply put, I do not know what cells in which to put the formulas that you offered as an answer. Believe me I appreciate your putting up with my lack of knowledge re: Excel 2007.

    Thanks again.

    Mel

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,056

    Re: Conditional Format cells if greater than or less than zero

    Hi Mel,

    The formulas do not go in a cell, they go in conditional formatting.

    You first highlight the range, so in your case, C38 and D38, then hold down the ctrl key and click on F38.

    After the cells and highlighted, you go to the ribbon starting with the home tab and follow the rest of the directions above.

    Searching youtube you can find many tutorials on using conditional formatting.

  7. #7
    Registered User
    Join Date
    02-06-2013
    Location
    Dade City, FL
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Conditional Format cells if greater than or less than zero

    Thanks for the help on this issue. I have learned a lot and it appears as if I have mis-stated what I really wanted.
    That is, I would like the font in cell c38, cell d38 and cell f38 to be black if f38 is positive or zero.
    As well,I would like cell c38, cell e38 and f38 to have a red font if f38 is negative. All backgrounds "no color".

    Previous guidance given is giving me a black cell fill and a red cell fill.

    Thanks again,

    Mel

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,056

    Re: Conditional Format cells if greater than or less than zero

    Hi Mel,

    Unless you changed the font colors on your spreadsheet, you really do not need condition 1 (black) and the default color is black.

    Anyway, in this example I added two rules, one for the black and one for the red.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-06-2013
    Location
    Dade City, FL
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Conditional Format cells if greater than or less than zero

    Thanks again, I finally got the conditional formulas to work. The reason behind the length of time it took me to get to the end was an algebra issue and nothing else.

    Thanks again.

    Mel

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,056

    Re: Conditional Format cells if greater than or less than zero

    Hi Mel,

    Glad to hear you finally have it working according to your requirements. Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1