Excel - Formatting currency with function (lose red negatives) 2

[pbbriggs]

Hi,

I have an Excel formatting question that I thought was pretty straightforward, but nothing seems to be giving me the desired result.

Basically, I want to combine a number (in one cell) with text in another cell, while retaining the correct formatting of the number. It is all working EXCEPT that my negative values are no longer appearing in red (as is typical with the Currency formatting and other finance formats).

My number is in A1 [let's say it's negative 10 dollars, appearing as ($10)]; my text is in B1 [let's say it's HELLO].

The basic formula would be =A1&" "&B1.
To retain the currency formatting I tried =DOLLAR(A1,0)&" "&B1

This works fine [resulting in ($10) HELLO] with the exception of the negative numbers. They appear in the correct format [i.e., ($10)]. But, the negatives (like the positives) are in black instead of red.

So I tried =TEXT(A1,"$#,##0_);[Red]($#,##0)")&" "&B1

Even with this explicit number format, the Red designation is ignored.

Can anyone think of a trick to get this to work?

Thanks!

 

[Gavona]

As far as I can see you can only have different font colours within a single cell when you have a string. So I can only come up with a VBA solution.

First convert the nunber to string then apply the formatting as required. I have assumed that the whole string will be less than 99 characters. I have assumed that the cell containing your concatenation is in column C in the same row as the value and text. So select C1 and run the code.

Sub Macro1()
Dim Mycolour As Integer
Dim Mylen As Integer

With ActiveCell
    If .Offset(0, -2).Value < 0 Then
        Mycolour = 3
    Else
        Mycolour = xlAutomatic
    End If
   .Value = ActiveCell.Value   'convert from formula to value
'or you could construct the formula within the macro eg:
'    .Value = .Offset(0, -2).Value & " " & .Offset(0, -1).Value
'assume that the red characters are all to left of the first space in the cell)
    Mylen = Application.WorksheetFunction.Find(" ", .Value)
    .Characters(Start:=1, Length:=Mylen).Font.ColorIndex = Mycolour
    .Characters(Start:=Mylen + 1, Length:=99).Font.ColorIndex = xlAutomatic
End With
End Sub

If this is the sort of thing that might help and no-one comes up with a better solution then post any questions, together with the code you are using to the VBA forum.

Note, there are no longer formulae in column C which may well be an issue. You could use code to refresh the values in this column every time someone changes column A.


Gavin

 

[SkipVought]

Hi,

Can't do it IN the formula.

However, you could do the font color change using Format/Conditional Formatting

Skip,
[sub]
[red][/red]
[/sub]

 

[pbbriggs]

Thanks, Skip and Gavin...

Right after I posted (isn't that always the way?) I thought of conditional formatting based upon the original value (A1). It works fine, and I can live with the red text it produces for the negative numbers.

I am going to try Gavin's code; I am not wild about losing the formulas as they turn to values but I'll see if I can work around that.

Thanks to both of you!

 

[Gavona]

Not sure if that is what is wanted Skip. I thought that he only wanted the number to be in red, not the text concatanated with it...


Gavin

 

[SkipVought]

Can't have BOTH a formula and partial color text.

If he want the formula, it all.

If he want partial, its no formula.

Skip,
[sub]
[red][/red]
[/sub]

 

[pbbriggs]

Gavin, your code is really slick. Works like a charm. I am going to write some quick code to revert back to formulas (and then re-run the formatting code) any time a value changes somewhere, but this was a great help.
Thanks.
Stars to you both - both solutions are equally viable depending on which limitation the user is more likely to accept!
Thanks again!