Conditional formatting or macros or vba?

**lcartwright** · 08-06-2014, 12:27 AM

Trying to find a solution to see if there is a way to format the numbers in a sentence to the colour red??

Example:

If (A1=100) & (B1=120)

="Today i scored "&text(sum($A$1),"0.00")&" and tomorrow i will try for "&text(sum($B$1),"0.00")

I would like the result to look like this:

Today i scored 100 and tomorrow i will try for 120

Any ideas?

**humdingaling** · 08-06-2014, 02:04 AM

replace D1 with cell you want output

Sub ConditionalText()

    Dim i, j, k As Integer
    
    Range("D1").Select
    
    ActiveCell.Formula = "Today i scored " & Format(Range("A1"), "0.00") & " and tomorrow i will try for " & Format(Range("B1"), "0.00")
    i = Len(ActiveCell)
    
    j = Len(Format(Range("A1"), "0.00"))
    k = Len(Format(Range("B1"), "0.00"))
    
    With ActiveCell.Characters(Start:=1, Length:=i).Font
        .Color = vbBlack
    End With
    With ActiveCell.Characters(Start:=15, Length:=j + 1).Font
        .Color = vbRed
    End With
    With ActiveCell.Characters(Start:=i - k, Length:=k + 1).Font
        .Color = vbRed
    End With
    
End Sub