Red(-ve)/Green(+ve) text for under/over monthly budget

Hello,

Newb to the forum.

I've devised a monthly savings sheet to allow me to track my savings towards a deposit on a house. My target is £xxx a month, but as I could contribute loose amounts of money I'd like to be able to display a field next to each month of how much over I've contributed or how much under I've contributed.

I'd like this to be represented as:-

If I contribute more than £xxx for a month then display it as green text and calculate how much over has been contributed.

If I contribute less than £xxx for a month then display it as red text and calculator how much under the contribution has been.

I'm really not sure how to do this, I would do it manually normally but surely there is a way of automatically representing it.

Look forward to your replies.

Mat

If A1 contains your contribution that month, then in B1 enter a formula like:

=A1-xxx ,where £xxx is your amount (or if that is stored in a cell, enter that cell ref).

Then copy down that formula to bottom of your range.

Then select the range from B1 downwards and go to Format|Conditional formatting:

Choose Cell Value Is and Less Than and then enter =0

Choose Format and choose Red from Font tab.

Click Ok

Click Add

Cell Value Is >> Greater Than >> =0

Click Format and choose Green from Font Tab

Click Ok

Click Ok.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Just on an aside, you could perhaps do this with usual Custom Formatting ?

If you apply a Custom Format to your range of calculations of:

[green]#,##0.00;[red]-#,##0.00;

or if preferred use the color index property of choice, ie for a little darker

[COLOR 10]#,##0.00;[red]-#,##0.00;

you should find the text adjusts appropriately...that said if you want to alter the background colour (fill) as opposed to just the font or in addition to it then Conditional Formatting is a definite must.