Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

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

I have a series of numbers in column C, both positive and negative. I'd like to change the font color of the cell with the highest number and the one with the lowest number. I may also want to change the font color of the rows, say from A-F. Separately, I may wish to change the background color instead of the font color (undecided). Help would be greatly appreciated.



How can I formulate a cell to either change font color or use different color highlights depending on the value of that cell?

ex. If the answer comes back a negative number, I would like the cell to highlight to yellow or if easier text to change to red from black (color is unimportant).

I am new to VBA and for years I would have like to be able to change the font color of an entire row based upon the value in a particular column.
I am using Excel 2010.
For example if the value of cell (anyRow, G) is negative then every cell in anyRow should have a red font color. Can you help?

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.

How do I get the font color to change automatically depending on whether it
is a positive or negative number? Example: if negative auto change to red,
If positive number change to green, zero balance auto change to black.

The code below changes the font color to the cell background color in BY795:CH1083 . Is there a way to change it it so that it only changes the font color in rows (in this range) where the value in the respective row in column CJ is greater than 3? There is a numerical value in each cell in CJ795:CJ1083. There are several different cell fill colors in this range.

For Each cell In Range("BY795:CH1083")
cell.Font.Color = cell.Interior.Color
Next cell

Example: If CJ800=4, the black font color in cells BY800:CH800 changes to gray (the cell fill color.)



I'm trying to make the font color of the selected range within a worksheet temporarily change to a specific rgb. When not selected THEN change back to original font color setting.

However I'm not very successful...

Paste these codes into worksheet event while having another worksheet activated.

I've have started with below code. This changes the cell color, not the font color. It works allthough not without some possibilities of imrovement.


  Const  activeFill  As   Long  = 255  '

I would like to be able to change the color of a value within a cell
depending on whether this is a negative or positive value. Thus - if my
calculation reults in a minus value e.g -2.7, I would like this displayed in
Red. If it is positive value, say 10 then I would like it displayed in green.
Any help or advice would very much appreciated


first i'm sorry about the confusing title - i wasn't quite sure how to phrase shortly what i'm trying to do.

here goes: in cell m4 i have the function =roundup(l4/a3,0).

l4 = 5760
a3 = 2

the result i'm getting is 2,880. Now in cell l4 is the function =k4-j4. My result is 5760 (positive)

I need to keep the function in m4 but i need it to change from green (positive) or red (negative) depending on the difference in l4. meaning if l4 is a positive then m4 needs to change to the color green and vice versa.

I've tried conditional formatting but maybe i'm just so twisted around i'm not seeing what I need to. any help would be appreciated. I dont know VBA so it would have to be in simple terms.

thanks and i hope i've made this easy to understand

Hello, All.

I am trying to code to change certain numbers from positive to negative and vice versa based upon the value in column A (I have attached a worksheet to illustrate what I am about to describe).

I have a list of accounts in Column A.
I have a list of amounts in Column B.

I am trying to code a macro to look at Column A and perform the following:

If A = 28XXX then the value in Column B should be a negative value.

If A = 395XX then the value in Column B should change accordingly (if positive, make negative; if negative, make positive

Any assistance would be greatly appreciated!

Thank you in advance.

Is there a way to make the sum of equation adopt a certain color based on its result? For instance if the result is a negative to make that number appear red and if it is a positive number to make it appear blue or any other color? Thanks for the help.


Hi how are you?
I have the following Visual Basic code that does not permit the printing of an Excel file:

[COLOR=navy][FONT=Courier New]Private[/FONT][/COLOR][FONT=Courier New] [COLOR=navy]Sub[/COLOR] Workbook_BeforePrint(Cancel [COLOR=navy]As[/COLOR] [COLOR=navy]Boolean[/COLOR])[/FONT] 
[FONT=Courier New] [/FONT] 
[FONT=Courier New]Cancel = [COLOR=navy]True[/COLOR][/FONT] 
[FONT=Courier New] [/FONT] 
[FONT=Courier New]MsgBox [COLOR=maroon]"You cannot print this book because its contents are confidential"[/COLOR], vbInformation[/FONT] 
[FONT=Courier New] [/FONT] 
[COLOR=navy][FONT=Courier New]End[/FONT][/COLOR][FONT=Courier New] [COLOR=navy]Sub[/COLOR][/FONT] 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Would it be possible to have a password, to enable the printing?
Thanks for your answers.

can i make the negative numbers in an IF formula automatically change color
to show the difference between negative numbers or positive numbers. (Red
negative, Blue positive)

Ok, this one I think is easy to implement, but i don't remeber how to do it.

I'm trying to implement a if function that will subtract two cells and if the number is negative it displays the result in red. If positive, it remains black. The thing is that I just can't remember what function i can use to change the color of the font, and not do it manually all the time. I'm using excell in english.

I am just looking for a keyboard shortcut macro. I change the font color in rows one at a time to either blue or red by selecting that row and using the format toolbar to select the color I want. Is there a macro shortcut for changing font color?

Even further, can the macro be able to change the font color of the entire row without highlighting the entire row? Meaning if I only have 1 cell selected within the row I want to color the font and apply the shortcut, can it color the font in the entire row???

Thanks for any help.

Here is what I would like to do

I would like a cell to have a fill color of bright yellow and bolded font IF a numerical value exists other then zero. If zero exists I would like a - to show with no fill color.

What I am doing
Bank reconciliations. So if a difference arises I would like my worksheet to show the cell box to be filled with yellow and bolded if a difference exists.

If the value is negative I would like this to be red font and if positive black font. I beleive nothing will have to be done for this matter.

I'm sure it's been covered before but I haven't been able to find it in the hour+ I've been searching. I'm trying to write a formula that changes the font color to red if the returned number is negative and' blue if the returned number is positive. Please HELP!


I'm trying to implement multiple conditional formatting in Excel 2003 for same cell. I've found threads that address similar issues, but I don't understand some of the syntax, and thus am unable to adjust it to my needs. Here is what I need done:

In a range of cells, e.g. B4 to AF9, do the following actions, depending on the content of the cell:

If Cell value is "H", then make the color of the cell, and font Neon (ColorIndex = 4?)
If Cell value is "N", then make the color/font of the cell Yellow (ColorIndex = 27?)
If Cell value is "S", then make the color/font of the cell Red (ColorIndex = 3?)
If Cell value is "C", then make the color/font of the cell Purple (ColorIndex = 17?)
If Cell value is "E", then make the color/font of the cell Gold (ColorIndex = 40?)
If Cell value is "R", then make the color/font of the cell Blue (ColorIndex = 32?)
Otherwise, leave cell blank

I found this post that relates: but I'm not sure how it defines the range of cells. In particular, what does "If Not Intersect(cell, Range("E:E,J:J,O:O,T:T")) Is Nothing Then" mean?

Any help would be much appreciated! Thanks

I am creating a time sheet. The cells used are A1 through K26.
When I enter the letters H, S or FD in any of the above cells I would like for that cell entry to change font color. All three of the above letters can be the same color if it would be easier to write a formula for that.

Also with the same range of cells I would like the change the color of the font for a numerical entry. Example I want to change the font color if the numerical entry is anything except the number 7.

Is this something I can use in conditional formatting?


hi guys, do u have a vba or a steps for conditional formatting for this? which is dependent on sheet number? example F4 value that i will input is different every sheet number...

if F4=6, the font color of D72:D73 & B104:I104 will become WHITE...
if F4=5, the font color of D70:D73 & B103:I104 will become WHITE...
if F4=4, the font color of D68:D73 & B102:I104 will become WHITE...
if F4=3, the font color of D66:D73 & B101:I104 will become WHITE...
if F4=2, the font color of D64:D73 & B100:I104 will become WHITE...
if F4=1, the font color of D62:D73 & B99:I104 will become WHITE...

NOTE: different value of F4 for every sheet number that is ranging from 6-1..
and the cell to be change in font color is the same as given above...

In a shared workbook, there are up to 20 people using the file and would like to know if:

Each person can be assigned a font color in a SPECIFIC cell (column F) so when they type, the font appears in their color? I already have the file to recognize the typer, so Im partially there...


If each time any cell in COLUMN F is updated, can I code the workbook to change the font color of each new entry to a different color?

The goal is to easily distinguish the original comment from additional comments because they are not always by the same person.


is there a formula to have a cells color change according to the content of the cell? what im looking to do is change the color of the cell according to the cell being a positive or negative number. Red if it is neg. and green if the cell is pos.

Hello board and thank you for all you do. I have searched many threads here to find an answer to most likely a simple issue that I have overlooked.

I am running the code below which works just fine except for 1 issue. When I run the code it does not copy over the first 3 rows of Header Data that I need in the new worksheets it creates. Surely I am missing something? Perhaps another offset?
Can someone please open my eyes to what I am missing?



[FONT=Verdana][COLOR=black]Sub Macro2()[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]Dim WS As Worksheet, R As Range[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]Application.ScreenUpdating = False[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]With Sheets("Test")[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black].Range("A1").AutoFilter field:=4, Criteria1:=">1"[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]For Each R In .Columns(4).SpecialCells(xlCellTypeVisible).Areas[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]If R(1).Row > 1 Then[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]Set WS = Worksheets.Add(After:=Worksheets(Worksheets.Count))[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]R.Offset(, -3).Resize(, 250).Copy WS.Range("A1")[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]WS.Name = Replace(R(1).Offset(, -2), ":", ",")[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]Next R[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black].AutoFilterMode = False[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]End With[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]Application.ScreenUpdating = True[/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]End Sub [/COLOR][/FONT] 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Hello all,

I am experiencing some really strange behavior when it comes to font color when the font is bold.
Lets say I have 3 cells that has 1,2,3 written into them.

If I choose another color then "automatic" and then make the font bold the first cell gets the actual chosen color but the next two gets a totally different color.

Has anybody experienced something like this and what could be the problem?

It is driving me crazy!

Any suggestions would be greatly appreciated!


Hi All
In my spreadsheet user enter first date of the month only to cell B3. The rest other date value of month will get by formula =IF(B3="","",B3+n) where n - day of month. Because that file will use for some years I formated cell M2 like date MMM so for any year that cell will display only three letters of month by formula M2 like =IF(B3="","",B3). The date values locate on ranges B3:K3, B12:K12, and B21:L21. And I would like when M2 has values "Apr", "Jun", "Sep", "Nov" font color of Range("L21:L28") change to white. If M2 has value "Feb" font color of Range("J21:L28") change to white. And if M2 has values "Jan", "Mar", "May", "Jul", "Aug", "Oct", "Dec" font color of Range("L21:L28") will change to black. That is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = ("$B$3") Then
        Select Case Range("$B$3").Value
            Case "Apr", "Jun", "Sep", "Nov"
                Range("L21:L28").Font.Color = RGB(255, 255, 255)
            Case "Feb"
                Range("J21:L28").Font.Color = RGB(255, 255, 255)
            Case "Jan", "Mar", "May", "Jul", "Aug", "Oct", "Dec"
                Range("L21:L28").Font.Color = RGB(0, 0, 0)
        End Select
    End If
End Sub

When I run the code and enter, for instance, to cell B3 value "4/1/2009" so M2 change own value to "Apr" but color of font in Range("L21:L28") doesn't changed. How to fix that problem?