colored IF

Excel Excel 2007 home and student
May 6, 2010 at 09:58:51
Specs: Windows 7

I was wondering about creating a formula that provides a specific color based on specified outcome? For example,something like, if cell A1 greater than cell B1, i want the formula to resturn the result and color (yellow)
Please let me know.


See More: colored IF

Report •

May 6, 2010 at 10:28:00
Look at Conditional Formatting.

1 - From the Ribbon select Home - Styles - Conditional Formatting,
2 - From the drop down select 'Manage Rules' and select 'New Rule'
3 - In the dialog box select the last item in the list 'Use a formula to determine which cells to format'
4 - Enter your formula in the box.
Something like =IF(A1>B1,TRUE,FALSE)
5 - Note that TRUE and FALSE do not have double quotes - they are Excel's logical values true and false.
6 - Click the format button and from the 'Fill' Tab select a color
7 - OK your way out.

Report •

May 8, 2010 at 02:48:15
I'm afraid it is not exactly what is needed. Basically, I want a cell to be blue when the number is positive, and red when it is negative. So for example, Cell J7 is 120, Cell I7 is 110, cell K7 will be displaying a difference (assuming in K7 will be a formula I7-J7). In this case the cell (k7) has positve 10. So I want the difference to be reflected not only by the sign but also by an automatic coloring of that cell. So, in this case I want this result (positive 10) formatted as blue cell. And there may be a lot of these cells, so some kind of automatic solution is needed. May be there is a simple macro, but I'm not so sure how to use them. What is the best solution to this please, aside from a macro?

Thanks for the previous response, it defintely works for other puposes.

Report •

May 8, 2010 at 07:30:22
re: definitely works for other purposes.

Unless I am misunderstanding your needs it definitely works for this purpose.

Conditionally Formatting was designed to do exactly what you appear to be asking for.

You can use multiple rules per cell, you can use multiple formatting per cell, you can assign it to ranges of cells in one operation, etc.

Using the steps I outlined earlier, the "rules" or formulae to use for K7 would be

Rule 1:

=IF(K7>0,TRUE,FALSE) Fill with Blue

Rule 2:

=IF(K7<0,TRUE,FALSE) Fill with Red

If you select K7:K25 all at once and apply the rules (using K7 in the formula) it will automatically adjust the Conditional Formatting rule for all of the selected cells. In other words, it will use =IF(K8>0, etc. for K8, =IF(K9>0, etc. for K9, and so on.

Based on the results of the formula in K7 (=I7-J7), the Conditional Formatting rule will set the fill color of the K7.

I'm not sure it gets more automatic than that.

Report •

Related Solutions

May 8, 2010 at 07:48:32
In this case the cell (k7) has positve 10.
No, K7 will be negative -10.


I7 - J7 = -10

As DerbyDad03 suggested, Conditional Formatting will get you what you want:

1) Select your cell: K7

2) On the ribbon click Conditional Formatting

3) Click on "New Rules", it’s near the bottom of the dialog box.

4) Click "Use Formula to determine which cells to format".

5) Enter the formula: =IF($K$7>0,TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select the color Blue

9) Click OK

10) Click OK

Repeat process for the Negative color Red

1) On the ribbon click Conditional Formatting

2) Click on "New Rules", it’s near the bottom of the dialog box.

3) Click "Use Formula to determine which cells to format".

4) Enter the formula: =IF($K$7<0,TRUE,FALSE)

5) Click on the Format button

6) Select the Fill Tab

7) Select the Color Red

8) Click OK

9) Click OK

Now if cell K7 has a Positive number, it will turn Blue, if it has a Negative number it will turn Red.


Report •

May 8, 2010 at 08:15:38

I appreciate your support of my suggestion.

I just want to point out one minor thing. I only mention this because the OP doesn't think Conditional Formatting will fit his needs and I want to make sure he enters the correct formula. I know you know this, but the OP might not.

While your instructions are perfectly accurate for Conditionally Formatting a single cell (K7), the OP mentioned this:

...there may be a lot of these cells...

So, when applying Conditional Formatting to a range of cells, don't use Absolute Referencing - i.e. no dollar signs.

If the user selects K7:K25 and uses =IF($K$7>0,TRUE,FALSE) as the rule, all of the cells in that range will be formatted based on the result in K7.

For multiple cells, use =IF(K7>0,TRUE,FALSE) and allow Excel to adjust the cell reference for each cell.

Report •

May 8, 2010 at 08:39:59
No problem, hadn't even noticed that I had used Absolute Referencing in my formulas.


Report •

May 9, 2010 at 00:03:28
This is very helpful guys, I was missing the possibility of entering two rules for a specific cell.

Thanks a lot.

Report •

May 9, 2010 at 07:01:05
I'm glad we could help.

I would like to take this opportunity to offer a posting tip.

If you'll go back and re-read your original post, you'll see that you asked a very specific question:

if cell A1 greater than cell B1, i want the formula to return the result and color (yellow)

Since we can't see your spreadsheet from where we are sitting, we had no way of knowing that you actually wanted more than one color based on different results.

It wasn't until your second post that you told us what you actually wanted to accomplish.

Had we known from the start the exact details of your task, this thread would probably contain only 2 posts, not the 8 that it currently does.

The 2 main rules to keep in mind when posting a question in any forum are these:

1 - We can't see your project from where we're sitting.
2 - We can't read minds.

The only information we have to work with are what we read in the post, so that is all that we can respond to.

Come on back if you have any other questions. We look forward to helping you in the future.

Office Forum Moderator

Report •

May 10, 2010 at 22:57:39
Will do my best next time. It also takes time to address a question properly.


Report •

Ask Question