VBA single cell multiple fonts

[elaineconnelly]

I have a spreadsheet with a single cells each containing 4 lines. The lines in the cell are split using Alt+Ent. I want the lines within each cell to be formatted by a different font colour. The top line red, the second line, blue, the third line red and the fourth line green.

I can do this manually by selecting each section of the cell and choosing the font colour but there are approx. 300 cells and I do not want to have to do this for each cell. Is there a way to achieve this by VBA? My data is in cells C3:Z20. The data needs to remain in one cell split into 4 lines - it cannot be entered onto individual rows

I am not a expert on VBA so an help would be great!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub FontCol()
   Dim Cl As Range
   Dim i As Long, j As Long, x As Long
   For Each Cl In Range("C3:Z20")
      j = 1: i = 1
      For x = 1 To 4
         i = InStr(j, Cl.Value, Chr(10))
         Cl.Characters(j, i).Font.Color = Choose(x, vbRed, vbBlue, vbRed, vbGreen)
         j = i + 1
      Next x
      Next Cl
End Sub

 

[elaineconnelly]

OMG! That is exactly what I was looking for
Thank you very much!!!

 

[Fluff]

Glad to help & thanks for the feedback