Combine two text cells, with different colors

Hi,
I have two cells with the two values
01.05.2012    and   -10,000
01.05.2012 has the color black
-10,000 has the color red.
(I have already converted them to text.).

I want to combine them in one cell and want to see 01.05.2012 black, -10,000 red.
Is it possible?

Best regards,

Cousin Excel

Answer
Answer
If you want to automate this for multiple rows in your Sheet, you can use the following Macro.

ASSUMING Date is in Column A (A2 & downwards), Amount is in Column B (B2 & downwards), To get merged values in Cell C2, Put your cursor over Cell A2 and Run the following Macro. The macro will automatically put the desired value along with color settings in the Cell C2 and position the cursor to Cell A3. You can run then run the  macro again to get output in Cell C3 and so on...


Sub Macro1()
'
' Macro1 Macro
'

'Starts on the first cell
Value1 = ActiveCell.Value
Color1 = ActiveCell.Font.Color
Len1 = Len(Value1)

ActiveCell.Offset(0, 1).Range("A1").Select
Value2 = ActiveCell.Value
Color2 = ActiveCell.Font.Color
Len2 = Len(Value2)

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = Value1 & " " & Value2
With ActiveCell.Characters(Start:=1, Length:=Len1).Font
    .Color = Color1
End With
With ActiveCell.Characters(Start:=Len1 + 2, Length:=Len2).Font
    .Color = Color2
End With
ActiveCell.Offset(1, -2).Range("A1").Select

End Sub

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer

If I understand correctly you want to the date in black and number in red in the SAME cell...

 

You can select part of the cell and change the color ...

 

If you want to use a macro then you have will you to use the Characters option;

 

expression.Characters(Start, Length)

expression Required. An expression that returns an object in the Applies To list.

Start Optional Variant. The first character to be returned. If this argument is either 1 or omitted, this property returns a range of characters starting with the first character.

Length Optional Variant. The number of characters to be returned. If this argument is omitted, this property returns the remainder of the string (everything after the Start character)


Example (if you have first value in A1, second in B1 and C1 has the combined value with a space in between

Sub Color_Part_of_Cell()

lenFirst = Len(Range("A1"))
lenSecond = Len(Range("B1"))

With ActiveCell.Characters(1, lenFirst).Font
.Color = RGB(255, 0, 0)
End With

With ActiveCell.Characters(lenFirst + 2, lenSecond).Font
.Color = RGB(0, 0, 255)
.Bold = True
End With

End Sub

 

If this response answers your question then please mark as Answer. It helps others who browse.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated July 26, 2020 Views 2,859 Applies to: