Change font color inside a concatenation in VBA based on the value

[Jesienouski]

I have a small macro that loops through and concatenates 4 different values into a single cell on worksheet. Some of the values could either be "Pass" or "Fail" as input into another part of the workbook. I would like the concatenation to have the "Pass" value to be green and the "Fail" value to be red within the concatenation. Below is the code

Sub BuildDocPackageLists()
Dim I As Integer
Dim R As Integer
Dim B As Integer
Dim C As Integer
B = 1
C = 17
 
Sheet6.Range("Q2:Y50").ClearContents
    Do Until B = 10
        Sheet6.Cells(26, 2).Value = Sheet6.Cells(B, 6)
            R = 2
            I = 2
            Do Until IsEmpty(Sheet5.Cells(I, 6))
                If Sheet5.Cells(I, 7).Value = "Yes" Then
                    Sheet6.Cells(R, C).Value = Sheet5.Cells(I, 1).Value _
                         & " - " & Sheet5.Cells(I, 2).Value & " - " & _
                         Sheet4.Cells(I, 3) & " - " & Sheet4.Cells(I, 6)
                    R = R + 1
                End If
                I = I + 1
            Loop
        C = C + 1
        B = B + 1
    Loop
Sheet6.Cells(26, 2).Value = Sheet6.Cells(1, 6)
 
    Application.ActivePrinter = "Adobe PDF on Ne03:"
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF on Ne03:"",,TRUE,,FALSE)"
End Sub

The values that are brought over from Sheet4 are the ones that would need to be colored. (Sheet4.Cells(I, 3) & " - " & Sheet4.Cells(I, 6)). Is there an easy way to acomplish this? The cells in Sheet 4 are already conditionally formated to color the font red and green if that helps.

Thanks!

 

[sijpie]

This is a little subroutine that I use to do something very similar: make certain words in a text bold. In my case I concatenate text from a form and add some words. Later these words need to be bold. SO I do a search for where theuy are using the function Instr(), which returns the start of the string I am looking for. You would be looking for the words Pass and Fail. You need to adapt it to your font colouring:

Private Sub EnterStatusText()
    Dim intStart As Integer, intEnd As Integer
    
   With ActiveCell.Offset(0, 15)
       .Value = "Status: " & ComboBox8.Value & " - " & TextBox7.Value & Chr(10) _
       & "Root Cause: " & TextBox8.Value & Chr(10) _
       & "Preventative Actions: " & TextBox9.Value & Chr(10) _
       & "Outstanding Actions: " & TextBox10.Value
        
       intStart = 7
       .Characters(Start:=1, Length:=intStart).Font.FontStyle = "Bold"
       intEnd = InStr(1, .Value, vbLf & "Root")
       .Characters(Start:=intStart, Length:=intEnd - intStart).Font.FontStyle = "Regular"
       .Characters(Start:=intEnd, Length:=12).Font.FontStyle = "Bold"
       intStart = InStr(1, .Value, vbLf & "Prevent")
       .Characters(Start:=intEnd + 12, Length:=intStart - intEnd - 12).Font.FontStyle = "Regular"
       intEnd = InStr(1, .Value, vbLf & "Outstan")
       .Characters(Start:=intStart, Length:=22).Font.FontStyle = "Bold"
       .Characters(Start:=intStart + 22, Length:=intEnd - intStart - 22).Font.FontStyle = "Regular"
       .Characters(Start:=intEnd, Length:=21).Font.FontStyle = "Bold"
       intStart = Len(.Value)
       .Characters(Start:=intEnd + 21, Length:=intStart - intEnd - 20).Font.FontStyle = "Regular"
      End With
    
End Sub

 

[Jesienouski]

It took some trying, but I was able to get that to work, THANK YOU!!!!