Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

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.

Thanks


jim


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

Thanks!


Hi!

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.

Code:

  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


Pinks



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.


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.


Hi,

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: http://www.excelforum.com/excel-prog...ther-cell.html 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


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

OR

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.

Thanks


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:
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?
Thanks.

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?

Thanks


Hello!
I have a question regarding shortcuts changing the font color of parts of words. I've attached an image in case my question is not clear.

I like to Bold (CTRL+B) and change the font color of only parts of words. For example: dogs -> dog s

I can do CTRL+B easily enough, but I was wondering if it were possible to find a way to do the same for the font color? Right now, I have to highlight the letters, then move my mouse all the way to the top left corner and click... it's a pain. Even better, is it possible to make a command that would let me bold and change the color at the same time?

I'd be happy just being able to move the Font color button closer to where I need them, but it seems stuck on the ribbon and I can't move it.

I tried to record a macro, but it would only work for an entire cell - I can't figure out how to make it work for just highlighted characters. I'm also pretty shaky with Excel, so I don't understand a lot of the coding jargon or advanced stuff.

Thanks for any help!


VB:

[B][COLOR=#3e3e3e][FONT=Times New Roman]Public Function FindSeries(TRange As Range, MatchWith As String)[/FONT][/COLOR] 
[COLOR=#3e3e3e][FONT=Times New Roman] [/FONT][/COLOR] 
[COLOR=#3e3e3e][FONT=Times New Roman]For Each cell In TRange[/FONT][/COLOR] 
[COLOR=#3e3e3e][FONT=Times New Roman]If cell.Value = MatchWith Then[/FONT][/COLOR] 
[COLOR=#3e3e3e][FONT=Times New Roman]x = x & cell.Offset(0, 1).Value & ", "[/FONT][/COLOR] 
[COLOR=#3e3e3e][FONT=Times New Roman]End If[/FONT][/COLOR] 
[COLOR=#3e3e3e][FONT=Times New Roman]Next cell[/FONT][/COLOR] 
[COLOR=#3e3e3e][FONT=Times New Roman] [/FONT][/COLOR] 
[COLOR=#3e3e3e][FONT=Times New Roman]FindSeries = Left(x, (Len(x) - 2))[/FONT][/COLOR] 
[COLOR=#3e3e3e][FONT=Times New Roman] [/FONT][/COLOR] 
[/B] 


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




=findseries(A1:A7,A1) or =findseries(A1:B7,"abc") where


A1:A7 contains the lookup values
A1 contains the text string you want to look for
B1:B7 contains the values you want to return

How would you get this to work if you were using the worksheet to pull from another worksheet in the workbook?

Tab 1
Column A - Item #
Column B - Purchase Order #
Column C - Purchase Request #

Tab 2
Column A - Item Number
Column B - function to return Purchase Order # or Purchase Request # but there might be multiple PO #s or PR #s associated so it would return values separated by a comma?


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,

Diggs


I was wondering if I could do a IF/Then statement with a font color change and simple calculation. Example, if I wanted to divide Cell B1 by Cell A1 and place the answer in Cell C1. If that number is 51% or higher make it blue, 49% or lower make it red, 50% stay black. I have done simple if/then statements where I would use seperate cell to say yes or no, but I was wanting to try to find a way to put the formula in C1 to both divide B1/A1 but also change the color of the font depending on the answer.

Is this possible??

Thanks.


Hi,

I have a set of 5 cells (a row vector), each cell containing a number from -1 to +1. Reading through the values from first to last, I want to count how many times it changes sign (i.e. from positive to negative or vice versa). Zeros are present but need to be ignored (skipped). For example:

-1 0 1 1 -1 would yield two sign changes (initial value is negative and it switches to positive and then back to negative)

0 0 1 1 0 would yield zero sign changes (first non-zero value is positive and it remains so)

-1 0 0 1 0 would yield one sign change

Obviously I could do this by hand for a small number of vectors, but I need to repeat this calculation across thousands of unique 5 number sets. Although my example above only uses values of -1, 0, +1, I may also need to generalize this such that the numbers may include several different negative and positive values (again, only sign changes are of interest however).

Any suggestions would be much appreciated. I am working in xl2010.

Thanks,
Howard


Hi,

I have a set of 5 cells (a row vector), each cell containing a number from -1 to +1. Reading through the values from first to last, I want to count how many times it changes sign (i.e. from positive to negative or vice versa). Zeros are present but need to be ignored (skipped). For example:

-1 0 1 1 -1 would yield two sign changes (initial value is negative and it switches to positive and then back to negative)

0 0 1 1 0 would yield zero sign changes (first non-zero value is positive and it remains so)

-1 0 0 1 0 would yield one sign change

Obviously I could do this by hand for a small number of vectors, but I need to repeat this calculation across thousands of unique 5 number sets. Although my example above only uses values of -1, 0, +1, I may also need to generalize this such that the numbers may include several different negative and positive values (again, only sign changes are of interest however).

Any suggestions would be much appreciated. I am working in xl2010.

Thanks,
Howard


Is there a way to combine these two codes below so that values from cells appear in that text box that results from DataValidation?

This code involving data validation causes a textbox to pop up rather than the standard comment box.

VB:

[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Dim strTitle As String[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Dim strMsg As String[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Dim lDVType As Long[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Dim sTemp As Shape[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Dim ws As Worksheet[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Application.EnableEvents = False[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Set ws = ActiveSheet[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Set sTemp = ws.Shapes("txtInputMsg")[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]On Error Resume Next[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]lDVType = 0[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]lDVType = Target.Validation.Type[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]On Error Goto errHandler[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]If lDVType = 0 Then[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]sTemp.TextFrame.Characters.Text = ""[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]sTemp.Visible = msoFalse[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Else[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]If Target.Validation.InputTitle <> "" Or _[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Target.Validation.InputMessage <> "" Then[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]strTitle = Target.Validation.InputTitle & Chr(10)[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]strMsg = Target.Validation.InputMessage[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]With sTemp.TextFrame[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial].Characters.Text = strTitle & strMsg[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial].Characters.Font.Bold = False[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial].Characters(1, Len(strTitle)).Font.Bold = True[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]End With[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]sTemp.Visible = msoTrue[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Else[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]sTemp.TextFrame.Characters.Text = ""[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]sTemp.Visible = msoFalse[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]End If[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]End If[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]errHandler:[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Application.EnableEvents = True[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][COLOR=blue][FONT=Arial]Exit Sub[/FONT][/COLOR][/FONT][/COLOR] 
[COLOR=#333333][FONT=Courier New][/FONT][/COLOR] 
[COLOR=#333333][COLOR=blue][FONT=Arial]End Sub[/FONT][/COLOR][/COLOR] 
 
[FONT=Arial]This code gets the DataValidation comment text from other cells In the workbook.[/FONT] 
 
[COLOR=blue][FONT=Verdana][FONT=Arial]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/FONT][/FONT][/COLOR] 
[COLOR=blue][FONT=Verdana][FONT=Arial]If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub[/FONT][/FONT][/COLOR] 
[COLOR=blue][FONT=Verdana][FONT=Arial]If Target.Count > 1 Then Exit Sub[/FONT][/FONT][/COLOR] 
[COLOR=blue][FONT=Verdana][FONT=Arial]With Target.Validation[/FONT][/FONT][/COLOR] 
[COLOR=blue][FONT=Verdana][FONT=Arial].InputTitle = Target.Offset(, 1).Value[/FONT][/FONT][/COLOR] 
[COLOR=blue][FONT=Verdana][FONT=Arial].InputMessage = Target.Offset(, 3).Value[/FONT][/FONT][/COLOR] 
[COLOR=blue][FONT=Verdana][FONT=Arial].ShowInput = Target.Offset(, 2).Value = "X"[/FONT][/FONT][/COLOR] 
[COLOR=blue][FONT=Verdana][FONT=Arial]End With[/FONT][/FONT][/COLOR] 
[COLOR=blue][FONT=Verdana][FONT=Arial]End Sub[/FONT][/FONT][/COLOR] 


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



Can these be combined so that the text in the textbox comes from other cells in the workbook?

Also, would it be possible for the text box to open right near the validated cell that is selected (rather than remaining in the same place on the sheet no matter what cell is selected.) Ideally, the bottom left corner of the text box would be one row above AND one column to the right of the validated cell that is selected.