Have been trying to figure out on my own how to change the value in a cell once it reaches a negative balance. Would like it to change to red once the variance balance is less than -0. I am comparing a budget total to the actual spend for a given month.
Jan Feb
Budget $4,308 $4,308
Variance -$5,204 $4,248
In this example the -$5,204 should change to red to reflect we went into the negative for that month.
Mike
use conditional formatting
use a formula
cell < 0
not sure how your data is formatted to give a full formula
perhaps a sample spreadsheet attached
Last edited by etaf; 02-14-2019 at 05:34 PM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
I type slow. Etaf beat me to it. LOL
Yes. It's called conditional formatting.
Suppose the data is in ... A2:A100. Select that range. Click Home > Conditional Formatting > New Rule > Use formula to determine which cells to format. Tab to Format values where this formula is true. Click on the upper most cell ... A2. Then press the F4 function key repeatedly until the default absolute referencing goes from $A$2 to A2. (I find it best to do it this way as manually typing that in can produce unpredictable errors ... I don't know why either.) Continue completing the formula as this =A2 < 0. Apply your formatting.
Click OK all the way out.
Dave
See attached example of the forecasting spreadsheet. Just want the Variance values to turn red if they come in as a negative value.
Are you talking about row-3 on the post #4 attached workbook?
If so, then if a value on this row in the range C3:M3 is negative then it already becomes red like so: ($5,204). Is it that you want the format to look instead exactly like -$5,204 ?
You already have conditional formatting which sets the number format to Currency with a negative number format of ($1,234).
Another couple of things: cell B3 and cell N3 are not formatted consistently with C3:M3.
Unless I'm missing something I don't think you need conditional formatting at all. You can simply set the number format appropriately. You will need, I think, a custom format like: $#,##0;[Red]-$#,##0
I modified your workbook by:
a) deleting all conditional formatting
b) Setting the font color of B1:N3 to "automatic"
c) Setting the number format of B1:N3 to: $#,##0;[Red]-$#,##0
I think this is what you were looking for. Let us know what you think.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks