Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

How To Make The Cell Or Font Color Red If The Number Is Negative?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I'd like to make a cell change color or the cell's font change color as the
number in the cell changes from negative to positive or vice versa?

View Answers     

Similar Excel Tutorials

Make All Numbers in a List Positive in Excel
Take a list of numbers and make them all positive, regardless of whether the list contains a mixed set of positive ...
Make Negative Numbers Positive in Excel and Vice Versa
I will show you a few ways to change negative numbers to positive numbers and back again in Excel. Don't forget to ...
Hide Data Within a Worksheet in Excel
In Excel you can actually hide data that is stored within a worksheet. This allows you to show data that is useful ...
Round Numbers Up or Down in Excel
How to round a number up or down and also to a specified number of decimal places in Excel.  This will allow you to ...
How to Add Formatting to Cells and Data in Excel Styles, Fonts, Colors, & More
In this tutorial I will cover how to use the various formatting tools in Excel. The Formatting tools are located on ...
Add Formatting to Comments in Excel
How to format the appearance of a comment and the text within it. Sections: Format Text within a Comment Format th ...

Helpful Excel Macros

Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
- This free Excel UDF allows you to output the color of a cell in text format or as that color's index number. Also note
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w

Similar Topics

Can anyone tell me if it's possible to change the tab color based on a formula in a cell on each tab? I want to change the tab color to red if the number in A1 is a negative number. If it's a positive number I just want it to remain the same, the default color. I have many tabs in the workbook and I'm using excell 2003. Thanks in advance.

I have an EXCEL bargraph that I would like to automatically show a green bar if the number is positive and a red bar if the number is negative. Just using the inverse color if negative does not give me the option of selecting the color. Is it possible to do what I am asking automatically, or must I manually set the color every time the value changes sign?


Good day (or night) to all,

I have three columns; c represents the actual number; d represents a forecasted number and e represents the variance between both (column d-column c).
If Column E ends up being a negative number or the % is negative((c-d)/d) I want the font in Column E to be green;
If Column E ends up being a % between 0 and 3 (e>0% and e3% I would like the color to be Red. Any ideas? I don't believe conditional formatiing will work. This will go for many rows. Thanks,


We have a spreadsheet with data downloaded from our mainframe.
We would like to change the negative numbers, which have a negative
sign before the number (i.e. -9.56), to positive numbers (i.e. ($9.56)).
Then we need to change the positive numbers to negative numbers.
Plus, we have a LARGE amount of data....

Can anyone help us?

Any suggestions? HELP!!!

Here's my formula... =SUM(S7)-T5

If that number is less than -100, I want it to show as blank.

Any help?

Right now, I'm trying to accomplish this by making a conditional format...meaning when my cell equals less than -100 I make the cell color and font the same (so it looks blank, but its not). When I print it, it still shows the negative value.


I'm trying to make a "weight-based" spreadsheet. I want to show a difference between numbers so if an item lost 5 lbs, there would be a "-5" and it would be in green font showing good.

For example, a box weighs 20 lbs and then drops down to 15 lbs. This would be a good result. So I would have 20 lbs in A4 and 15 lbs in A6 and I want to show the difference in A8. However, since I want the box to lose weight, I want it to show a negative value of "-5" in green font meaning it is good. And vice versa, I want it to show "+3" in red font if the box gained weight.

I can't really figure out how to get the signs working properly. I'm assuming to use an "if" function in where if the difference is a loss, show the negative sign and have it in green font. And if the difference is a gain, show the positive sign in red font.

I hope this wasn't confusing.


I'm having a hard time figuring out how to use the conditional formatting with a negative percentage. What i'm doing is taking two percentages and finding the differance (if there is one).

If there is a positive differance then I would like to color the cell green. (Example: anything greater than 0, like 5%)
If there is a negative differance I would like to color the cell yellow. (Example: anything less than 0, like -3.15%)
If there is a differance of -6.25% I would like to color the cell red. (Example: anything less than -6.25%, like -15%)

Everytime I try and do it the formula doesn't work. Any suggestions?

Thanks -

In Cell (W6) I have a value for example of 100,000 In Cell (AE6) will have a value of for example -100,000. In Cell (AF) I want to put a formula that says "if the number in (W6) is a positive number and the number in (AE6) is a negative number the return a value of "1". and vice versa if the number in cell (W6) is a negative number (-100,000) and number in Cell (AE6) is s positive (25,000) then also return a value of "1". If any one can help me I take this oppurtunity to thank you in advance as I having difficulty figuring this out.


I am working on a spreadheet in which the numerical value in cell A1 are copied and pasted from another worksheet. The value can increase and decrease over time.

Also, the value may sometime have a negative value hence when the numerical number gets higher with a negative sign, its decreasing, and vice versa.

I would like to know if there is a way to change the cell colour based on the increase/decrease data (e.g. green when increase, red when decrease).

I cannot fill-in a cell with a color or format the color of the font. I
verified that this I can change font color in Word. So it appears to only be
happening in Excel. Already tried relaunching and rebooting computer.
Anyother ideas or places to look where I may have changed a setting?

How do you automate arrows in Excel?

I am trying to have a cell show a green upward facing arrow for positive numbers and a red negative downward facing for negative numbers. I tried to use conditional formatting for this, but first of all, I do not know how to have this refer to an adjacent cell to do the conditional formatting. Also, for conditional formatting, I only know how to change the color of the cell or characters using this method. I then try using an if statement, but it also does not work.
I use letters because I am using the Marlett font.

=IF(I13>o, t, u)

Any help would be greatly appreciated. Thanks.

After realizing that the ability to control a Bar Charts color by way of the "Invert if Negative" setting to something other than white is no longer available in Excel 2007 I decided to write a code to handle the need.

FYI, for those that don't know, you can't access the "Fill Effects" settings under the "Format Data Series" dialog box in Excel 2007. So now there is no way to set the "Color 1:" and "Color 2:" settings, which allowed you to essentially set the "Invert" color for negative values. Seriously frustrating!!!

Often I like to use bar charts to display performance to prior periods and It's especially nice to have the positive series bars a color like green and the negative series bars red.

I've come to an issue and I'm receiving the dreaded run-time error 13 (type mismatch) and I need some guidance. It's the "If" line that is highlighted in debug mode and apparently causing the error.

Here is the code...


  Sub InvertToRed()  
     '   Written by Dan DeHaven on 9/3/2009  
     '   Use this code to change the ActiveCharts Bar or Column color to red if the value of  
     '   the Series is negative  
     Dim s As Series  
     With ActiveChart  
         For Each s In .SeriesCollection  
             If s.Values < 0 Then s.Interior.Color = RGB(255, 0, 0)  
         Next s  
     End With  
  End Sub  

Can anyone tell me what I've done wrong.

Or I'm always open to suggestions for better code.



I have two columns, both containing some positive and some negative #s.

Right now, I have =COUNTIF($V$1:$V$57,"<0") to count the number of times there are negative values, and ">0" to count the positive values for each column.

I want to compare the two, to get a count of any time that a negative value in column X corresponds to a positive value in column V, and vice versa.

For example:

200 (50)
431 542
(43) (56)
(43) 54
75 (13)
and so on.

So, in this case, I would like a function that gives me a count of the 2 times V is positive and X is negative, and the 1 time that X is positive and V is negative.

Let me know if you have any other questions - thanks!

Is there a way I can use conditional formatting or something to change the color of the cell once I enter a value or text into that cell? For example if I currently have the A2 cell color as a gray color, and I enter information into A2 can I have it set to automatically change to no fill or any other color without having to click out of the cell, back into it, and then clicking the button?? Does that make sense??? Can someone help me?

First off, I know nothing about Conditional Formatting...

Is it possible to have a cell either change color or change font color or go bold when a formula is true?

Example: A persons goal is 200. On jan 1 they only have 180, on jan 2 they have 250. I would like to have the 250 be represented that they are "over" goal. I would use the over/under wording but I still need to see the number in the box....

Thanks in advance, your past helps have been a godsend!

Dear Excel / VB Experts,

I want to change color of each row in data range A1:AF500 based on the number in respective row in the AG Column. For example, the color of row A1:AF1 should change with the number entry in cell AG1 and so on for all the rows till row 500. I know this can be easily achieved with Conditional Formatting but the hitch is my conditions are more than 3. The conditions are as follows,
If Cell Value is 5 then the respective row color has to be changed to Turquoise (Color Index 8) If Cell Value is 6 or 7 or 8, then the respective row color has to be changed to Lavender (Color Index 39) If Cell Value is 9 then the respective row color has to be changed to Yellow (Color Index 6) If Cell Value is 10 then the respective row color has to be changed to Green (Color Index 4) No action to be taken if the cell contains any other value than the conditions listed above I do not know VB much so I searched through the Mrexcel Archives and Google but could not get one code which could satisfy my needs. There are some codes available on net and in Mrexcel but not being a VB Expert, I am not able to edit them.

Can somebody help me with a simple VB program which I can use to achieve this?

Thanks a ton in advance,


How can I vary the color in an area chart between positive and negative data
points. Using zero as the defining level, anything greater than zero have
one color and anything less than zero having a different color. Thank you
for your help.

Good morning all. I hope you can help me with my problem. I want to change the font color of individual components in an axis. The horizontal axis at the bottom of one of my charts shows three groups of names. I'd like for each group to be a different color for easier viewing. Can this be done? I can change the chart column colors if necessary, but I'd still like to know if it's possible to mix the font colors in the axis.


I have a spreadsheet with column that has + or - Pct in it. I have used conditional formatting to change the font to red whenever the data is -. I have a chart and this + or - Pct column is a Secondary Axis in the chart. What I would like to happen is the font color change to red if the Secondary Axis is a - Pct. Is this possible? If so, how can get the negative to turn to red on the chart?

I currently have a range of numbers which need to be change from a positive
into a negative however I am not aware of the cell reference as the rows can
be more or less.

Does anyone know how to change a range of numbers from a positive into a
negative using VBA.

I actually need the number to be negative not just the format.


Can anyone tell me what the purpose of xlAutomatic is for in Font Color? I've scripted some code that allow a user to change the cell background color. My trouble is when the user chooses "Black", then the font doesn't show. I was hoping that "Automatic" meant that it would turn to white so that text was visible. Wrong! Is there anyway to accomplish this without writing specific code for dark colors? thanks,

Hi everyone, I have a pivot table that I have attached below. When I originally created the pivot table, I didn't know anything so I totally hacked my way through it and it still works for me at work on the 2003 version of excel which everyone still likes.. I then sent the file home to review on my 2007 version. Well the screen colors at home are vastly different than the colors at work. Originally the pivot table was set up at work and most of the font colors are actually white. At home excel has decided that even though the screen background is white, it doesn't want to display the information in a cell that has a font color that has the same color as the background color. I can manually change the colors, but as soon as I pivot to a different set of data, the cell colors will revert back to the original colors.

So my question is, how do I change the font color for all the information in the pivot table in a global manner? I know you can do it, because I it is all one color now, but I can't seem to find a way to do it now.

And one more thing, why doesn't excel display the information anyway, and let me worry about discerning it from the background color

Hi all,
I would like to change the default font color (black) to red...
This is possible?
The idea is to make appear the messages on Application.StatusBar more visible.
Thanks in advnace and best regards,

Hi everyone,

I have a tedious task of highlighting and changing the fill color of certain cells. There is a specific pattern that needs to be changed everytime.

Since the color is always consistant throughout the certain cells, I would like to make 1 cell be the master cell and every other cell in my pattern copy the color fill of the master.
Therefor if I change the color of the master cell, every other cell in the pattern would automatically change itself to the same fill color.

Would there be a general solution to this with an equation or would it involve writing a macro for it.

Thanks in advance

Hey guys,

I am wanting to change a cell color on one sheet based on a value contained in a cell on another sheet after looking up a players name...does that make sense?? Check out the attached spreadsheet.

The cell i want to change color is D6 on the panel sheet.

I need it to look at the players name at J2, then I guess vlookup that name on the next sheet...if that player has a value in the Y column (either a 1, 2 or 3) then the cell color at D6 will change to red.

Any help would be greatly appreciatted.