Partial colour text with Excel Formula?

[kgkev]

I am dragging some data from various sheets and presenting it on a single line

=IFERROR(VLOOKUP(H5&"|"&B5&"|"&D5,comments!$A:$B,2,FALSE)&" ("&TEXT(((VLOOKUP(H5&"|"&B5&"|"&D5,comments!$A:$D,4,FALSE))),"dd/mm/yyyy")&")",Z5)&IF(AE5<>""," //-// Picking info: "&TEXT(AE5,"dd/mm/yy")&" "&AF5,"")

If the last part contains some text I would like it to be coloured red

=IFERROR(VLOOKUP(H5&"|"&B5&"|"&D5,comments!$A:$B,2,FALSE)&" ("&TEXT(((VLOOKUP(H5&"|"&B5&"|"&D5,comments!$A:$D,4,FALSE))),"dd/mm/yyyy")&")",Z5)[COLOR=#ff0000]&IF(AE5<>""," //-// Picking info: "&TEXT(AE5,"dd/mm/yy")&" "&AF5,"")[/COLOR]

is that possible?

 

[Fluff]

Not that I'm aware of without the use of VBA.

 

[Kamolga]

Hi,

To me this is only possible with VBA. Select all cells that have your formula and run this macro

Sub RedToEndOfCell()
On Error Resume Next
 For Each cell In Selection
    If InStr(cell.Value, " //-// Picking info:") = 0 Then
      Else
        With cell.Characters(Start:=InStr(cell.Value, " //-// Picking info:"), Length:=Len(cell.Value) + 1 - InStr(cell.Value, " //-// Picking info:")).Font
        .Color = -16776961
        End With
    End If
 Next cell
End Sub

 

[kgkev]

it doesn't seem to work for me.
I have slightly modified it, and it still lands on the With statement in the correct place, but the colour is not updated.

Sub colourred()



    For Each cell In Range("M5", Range("B56000").End(xlUp))
        If InStr(cell.Value, " //-// Picking info:") = 0 Then
        Else
            With cell.Characters(Start:=InStr(cell.Value, " //-// Picking info:"), Length:=Len(cell.Value) + 1 - InStr(cell.Value, " //-// Picking info:")).Font
                .Color = -16776961
            End With
        End If
    Next cell
End Sub

any ideas?

 

[RoryA]

You can't colour part of the result of a formula. You'd have to replace the formula with its value and then colour the relevant section(s).

 

[kgkev]

Thanks for the heads up Rory,

I have moved the formula to end of the sheet and added a pastespecial back to "M"

unfortunately its still not working.

Sub colourred()

    Range("AG5", Range("AG56000").End(xlUp)).Copy
    Range("M5").PasteSpecial xlPasteValues
    For Each cell In Range("M5", Range("B56000").End(xlUp))
        If InStr(cell.Value, " //-// Picking info:") = 0 Then
        Else
            With cell.Characters(Start:=InStr(cell.Value, " //-// Picking info:"), Length:=Len(cell.Value) + 1 - InStr(cell.Value, " //-// Picking info:")).Font
                .Color = RED
            End With
        End If
    Next cell
End Sub

 

[RoryA]

Have you declared RED somewhere?

 

[Fluff]

Also try

For Each cell In Range("M5", Range("[COLOR=#ff0000]M[/COLOR]56000").End(xlUp))

 

[shg]

Sub ColorRed()
  Const sWhat       As String = "//-// Picking info:"
  Dim rFind         As Range
  Dim iPos          As Long
  Dim sAdr          As String

  Range("AG5", Range("AG56000").End(xlUp)).Copy
  Range("M5").PasteSpecial Paste:=xlPasteValues

  With Range("M:M")
    Set rFind = .Find(What:=sWhat, LookIn:=xlValues, LookAt:=xlPart)

    If Not rFind Is Nothing Then
      sAdr = rFind.Address
      Do
        rFind.Select  ' remove when you're happy
        iPos = InStr(rFind.Value, sWhat)
        rFind.Characters(Start:=iPos, Length:=100).Font.Color = vbRed
        Set rFind = .FindNext(rFind)
      Loop While rFind.Address <> sAdr
    End If
  End With
End Sub

 

[Rick Rothstein]

Have you declared RED somewhere?

Or just change the RED to vbRed (which is the built-in color constant for the red color).