# Conditional Format cells if greater than or less than zero

1. ## 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

2. ## Re: Excel General

• We would love to continue to help you with your query, but first, before we can proceed…
• 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

3. ## Re: Conditional Format cells if greater than or less than zero

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. ## 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. ## 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. ## 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. ## 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. ## 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.

9. ## 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. ## 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.

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

#### 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