justify cell based on value?

[auntie bella]

Hello all,

I have managed to find other posts on here which allow me to show credit vaules as red and with curly brackets using format

#,##0.00;[Red]{#,##0.00};0.00 (seems to work correctly)

but given that my column will contain both debits and credits is it possible to justify credits left and debits (and zero) right?

thanks

 

[Gerald Higgins]

Not easily, within your basic formula and format options.

Three possibilities
1) Use a formula to insert spaces either before or after the number, depending on whether it's negative or positive, to give the illusion of left and right alignment.
This should be reasonably easy to implement, but there are serious problems with it - it may be difficult to get the numbers to line up just how you want them, and most importantly, inserting spaces will make it difficult to use the numbers as inputs to other arithmetical formulas.
2) Use separate columns, one for negative numbers, one for positive, and use formulas to input numbers into each column, for example

=if(a1< 0,a1,0)

and so on.
3) Use VBA solution to align each cell, based on it's value.
If you're interested in this solution, post back and I'll see if I can knock up some code.

 

[auntie bella]

Thanks for reply.....

yes I am interested if you have time to provide something - or set me away with basics.... I would still want the values to be numbers as they will be "summed". It would just make things easier after printing in black and white to easily run the eye down the column and notice the credit values....

Cheers!

 

[Gerald Higgins]

Something like this ?

Sub Align()
    Dim Cell As Range
    Dim Rng1 As Range
    Range("A1:A100").Select
    Set Rng1 = Selection
    For Each Cell In Rng1
        Select Case Cell.Value
        Case Is < 0
            Cell.HorizontalAlignment = xlRight
        Case Is >= 0
            Cell.HorizontalAlignment = xlLeft
        End Select
    Next
End Sub

 

[auntie bella]

Many Many Thanks.... worked like a dream....


(ashamed at how "easy" this was - I know easy for me to say when you did the work!!!
but.... with hindsight I could have written something like this but got lost in the detail of what I wanted and suffered a mental block). Thanks again for the time and effort - Regards....