Formatting cells in excel

Msoft / Win xp pro
April 3, 2009 at 12:45:17
Specs: Windows XP
Is there something I can add to an IF...THEN statement to change the numbers from black to red. I am using Excell 2007 version.

See More: Formatting cells in excel

Report •


#1
April 3, 2009 at 13:11:45
That would depend on what your doing with the numbers.

If you check the Custom Formats there are ones like

#,##0_);[Red](#,##0)

That will change to red with a negative number.

Also under the Numbers format you can specify Red if a number is negative.

Then there is always Conditional Formatting which will do the job.

MIKE

http://www.skeptic.com/


Report •

#2
April 8, 2009 at 17:45:52
I want it to trigger if the number exceeds a certain amount. I can use either a change in the color of the number or the cell fill.

And, thanks for the quick reply.

John


Report •

#3
April 8, 2009 at 17:51:06
Have you looked at Conditional Formatting? That's exactly the type of situation it was meant for.

Your only other choice is a macro, but I don't see any need for that based on what you asked for.


Report •

Related Solutions

#4
April 8, 2009 at 18:46:20
Actually, there is one other choice besides a macro & conditional formatting, there is actually a cell format solution.
But, you'll have to divulge more information if you want us to help.

MIKE

http://www.skeptic.com/


Report •

#5
April 16, 2009 at 05:28:31
Mike,
I am setting up an inventory sheet for various locations. Just a simple spreadsheet. Much of the items on the SS are throw-aways. I want to track the cost of the items to see if there is a cvase for repairing rather than replacing.
By identifying items by cost, I want the cell to change to identify high cost rather than high volume.

Hope that helps


Report •

#6
April 16, 2009 at 05:54:50
I could be wrong, but when Mike suggested "you'll have to divulge more information if you want us to help" I believe he meant some specifics as to what to base the cell formatting on, such as greater than a certain value, highest numbers in a list of numbers, etc.

In an earlier post you said "I want it to trigger if the number exceeds a certain amount" which is why Conditional Formatting was suggested.

Perhaps if you gave us some examples, as well as telling us why Conditional Formatting won't work for you, we can offer other suggestions.


Report •

#7
April 16, 2009 at 08:30:48
As DerbyDad03 says, I was asking for some examples on which to base my suggestion.

As a start here is a way, using Custom Formatting, to accomplish your request and it does not require any IF statements.

On the Home Ribbon
Format
Format Cells
Custom

Replace the word “General” in the small input box below the word “Type:” with:

[Red][<10];[Green][>=10]

This will display any number less than 10 as Red
And any number greater than or equal to 10 as Green

Very limited in its use.
It’s an either/or situation and the “fun” part is if your number falls outside the range you have specified, it does not show up at all, simply disappears.

MIKE

http://www.skeptic.com/


Report •

#8
April 16, 2009 at 09:37:24
Mike, didn't know about that method.

I played around with it a bit and the first thing I noticed was that (in 2003 at least) after I pasted your format string into the Custom format box, and then went back and looked at it later, it read:

[Red][<10]General;[Green][>=10]General

Just interesting, that's all.

re: if your number falls outside the range you have specified...it simply disappears.

I assume you know this, but for the benefit of others, I'll pass along what else I learned...

If you add a ;General at the end of the string, the "disappeared" numbers (i.e. numbers outside the specified range) show up again.

To see numbers 10 <= n < 20 use:

[Red][<10]General;[Green][>=20]General;General

And finally, riddle me this:

Exactly which numbers would be outside your range of <10, >= 10? ;-)


Report •

#9
April 16, 2009 at 09:50:56
The final note was cautionary in nature, for if you had entered it as:

[Red][<10];[Green][>10]

which at first glance looks OK,

What happens when you enter the number 10?

Also to get a tri color response you can do something like:

[Red][<10]General;[Green][=10]General;[Blue]General

An to make your TEXT different try:

[Red][<10]General;[Green][=10]General;[Blue]General;[Yellow]General


MIKE

http://www.skeptic.com/


Report •

#10
April 16, 2009 at 12:48:17
Thanks for the help. I remember doing it before (many years ago) and it may have been in Lotus 123.
I will try your suggestions as soon as I get back to work. As a test, Column A is a description of the item, Col B is the cost, Col C is the qty of units and Col D would be B*C. Each line item would be of different value so the limit would have to be set individually.
Thanks again for the help.

Report •

#11
April 16, 2009 at 13:17:45
OK, here’s a simple Conditional Formatting solution:

1st – Select the cell D1

On the Menu Bar:

2nd - Format
3rd - Conditional Formatting
4th – Change “Cell Value is” to “Formula Is”
5th – Enter the formula:

=IF($B1*$C1>=5,TRUE,FALSE)

Sub Menu Format :
6th - Patterns
7th - Choose a Pretty Color
8th - Press OK
9th - Press OK

Select cells D1:D15.
Then "Edit", select "Fill" on the Edit menu, and then click “Down”.

Now any value in cells D1 thru D15 that is greater than or equal to 5 will Automatically turn your selected pretty color.

Again, this is only a very simple solution.
More complex formulas are also possible.

MIKE

http://www.skeptic.com/


Report •

#12
April 16, 2009 at 13:35:30
Just as an additional note, my previous example made the assumption that you had already entered the formula =B1*C1 into the D1 cell, the Formatting still works if you haven’t, but it simply changes the color of a blank cell without the formula.

Also, you could have done something like:

1st – Select the cell D1

On the Menu Bar:

2nd - Format
3rd - Conditional Formatting
4th – In “Cell Value is” in the First input box, change it to read “Greater Than or Equal To”
5th – In the next Box enter the number 5

Sub Menu Format :
6th - Patterns
7th - Choose a Pretty Color
8th - Press OK
9th - Press OK

Select cells D1:D15.
Then "Edit", select "Fill" on the Edit menu, and then click “Down”.

Now any value in cells D1 thru D15 that is greater than 5 will
Automatically turn your selected pretty color.

This is getting to be a tutorial on how to do Conditional Formatting.......

MIKE

http://www.skeptic.com/


Report •

#13
April 16, 2009 at 14:53:24
re: Each line item would be of different value so the limit would have to be set individually

Perhaps...perhaps not.

If the "limit" had some type of consistent mathematical relationship to each line item, then a single Conditional Formatting formula might still work.

e.g. =IF(D1>B1*6,TRUE,FALSE)

Another option would be to put the values in a separate column and then let the CF formula compare D1 to that cell. Putting a bunch of "random" values in a column of cells might be easier than creating a bunch "random" CF formulae.

You could then hide the column of values used for the CF.


Report •


Ask Question