How to color code a cell when using the & function in excel

[Mark McInerney]

Hi,

I have data in Cell A1 and A2, 4 and 8 respectively.

In cell A3 I need to combine both values - I am using =(A1&A2). I want both values to be formatted as numbers and I want the second value (i.e. A2 to have red text - appear in red).

Any help appreciated.

Thanks - Mark.

 

[MrTeeny]

Don't think it can be done with formulas or conditional formatting and also I don't think you can change the font colour with a Function so you'd probably have to look at using something like a worksheet_change event to kick off your vba. Also not sure of why you insist on the values to be formatted as numbers as excel treats numbers enclosed in brackets as a negative number, is that the reason for insisting on numbers? Excel will still allow you to treat the values as numbers. Maybe this will be of use or not but at least it'll get the thread started, the contents in A3 can still be treated as a number and still be added etc +A3 and you can always change the alignment to left if needed

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo xit:
Application.ScreenUpdating = False
Application.Calculation = xlCalculateManual
Dim strlen1 As Integer, strlen2 As Integer


If Not Application.Intersect(Target, Range("A1:A2")) Is Nothing Then
Range("A3").Font.ColorIndex = 1 ' reset colour
 
Range("A3").Value = "'(" & Range("A1").Text & Range("A2").Text & ")"


strlen1 = Len(Range("A1").Value)
strlen2 = Len(Range("A2").Value)


Range("A3").Characters(Start:=strlen1 + 2, Length:=strlen2).Font.ColorIndex = 3
End If
  
xit:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[FDibbins]

MrTeeny is correct, you cannot format part of a cell with anything other than VBA (or manually)