Make the font red if derate or - and green if uprate or +

[Nlhicks]

This code does what it is supposed to do such that it returns this:

What I want it to return is this:

Public Sub Bold_in_Concatenate1()
' https://www.mrexcel.com/board/threads/run-my-master-workbook-and-call-another-non-macro-enabled-workbook-and-do-the-edits-to-it-prior-to-saving.1223414/
' Updated: 20221202
' Reason:  Reworked Code
    Dim wbkData           As Workbook
    Dim wksWork           As Worksheet
    Dim blnEnd            As Boolean
    Dim lngTarget         As Long
    Dim wksWorkOn         As Worksheet
    Dim strWbVersion      As String
    Dim wbkTarget         As Workbook
    Dim wksFrom           As Worksheet
  

    Const cstrPath        As String = "C:\Users\nhicks\Documents\Ratings\Saved Versions\"
    Const cstrWbData      As String = "WAPA-UGPR Facility Rating and SOL Record (Master).xlsm"
    Const cstrShData      As String = "Line Update"

    Const cstrStFileName  As String = "WAPA-UGPR Facility Rating and SOL Record (Data File)_v"
    Const cstrShFacility  As String = "Facility Ratings & SOLs (Lines)"
    
        GetWorkbook_Worksheet cstrPath, cstrWbData, wbkData, cstrShData, wksWork

  If wbkData Is Nothing Then
    MsgBox "No Object set for '" & cstrWbData & "'. ", vbInformation, cstrMsgTitle
    blnEnd = True
    GoTo end_here
  End If
  If wksWork Is Nothing Then
    MsgBox "No Object set for '" & cstrShData & "'. ", vbInformation, cstrMsgTitle
    blnEnd = True
    GoTo end_here
  End If
  
  With wksWork
    'assuming that the cells are all located on the same sheet
    '??? Range("Q13") is used two-times ???
    .Range("D32").Value = ("(" & .Range("L11") & " " & .Range("K13") & " " & .Range("L13") & " " & .Range("Q13") & " " & _
        "," & " " & .Range("O11") & " " & .Range("N13") & " " & .Range("O13") & " " & .Range("Q13") & ")")
    .Range("D32").Font.Bold = True
    
  End With
  GetWorkbook_Worksheet cstrPath, strWbVersion, wbkTarget, cstrShFacility, wksWorkOn

'  With wksWorkOn
'    lngLastRow = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1).Row
'    wksFrom.Range("J13").Value = .Range("A2:A685").SpecialCells(xlCellTypeVisible).Value
'
'End With

end_here:
  Workbook_Worksheet2Nothing wbkData, wksWork
  If blnEnd Then End
  
Call Show_Update


End Sub

 

[Micron]

I colored the table this time and produced the email and the font was still black

You never mentioned that you were trying to get this colouring in an email body.
THAT is the issue? AFAIK that can only be done on the email side IF the body text type is rtf or html and you would have to code the cell content as rtf.

 

[Nlhicks]

You never mentioned that you were trying to get this colouring in an email body.
THAT is the issue? AFAIK that can only be done on the email side IF the body text type is rtf or html and you would have to code the cell content as rtf.

 

[Nlhicks]

rtf?

 

[Micron]

I don't find that funny. You have a search engine - look up rtf.
Good luck.
Un-watching this one.

 

[HaHoBe]

Hi Nicole,

regarding the opening post which is about formatting the contents in a cell on a worksheet I must admit I didn't expect the concatenation to hold both of the items you want to be coloured. So the code will be a bit more lengthy in order to get the result which may be wanted:

'additional variables needed in next part
    Dim varSplit          As Variant
    Dim lngSplit          As Long
    Dim lngStart          As Long
    
    With wksWork
      With .Range("D32")
        .Value = ("(" & .Range("L11") & " " & .Range("K13") & " " & .Range("L13") & " " & .Range("Q13") & " " & _
            "," & " " & .Range("O11") & " " & .Range("N13") & " " & .Range("O13") & " " & .Range("Q13") & ")")
        .Font.ColorIndex = 1
        .Font.Bold = True
        strHelp = Mid(.Value, 2, Len(.Value) - 2)
        varSplit = Split(strHelp, ",")
        For lngSplit = LBound(varSplit) To UBound(varSplit)
          If lngSplit = UBound(varSplit) Then
            lngStart = InStr(1, .Value, ",") + 1
          Else
            lngStart = 2
          End If
          If InStr(1, LCase(varSplit(lngSplit)), "downrate") > 0 Then
            .Characters(Start:=lngStart, Length:=Len(varSplit(lngSplit))).Font.ColorIndex = 3
          ElseIf InStr(1, LCase(varSplit(lngSplit)), "uprate") > 0 Then
            .Characters(Start:=lngStart, Length:=Len(varSplit(lngSplit))).Font.ColorIndex = 10
          End If
        Next lngSplit
      End With
    End With

In cell D32 a lot of information is gathered. First thing is to make the colour for the font used black (this will be used for the brackets and the comma). Next we build a part excluding the brackets and split that part by comma to get two strings for checking. Each string is checked and if one of the items is found the characters for this string will be coloured accordingly.

I hope to have it right this time for the opening request of this thread. And I would expect any other subject in your project to be located in a thread of it's own once this thread is fully solved.

Ciao,
Holger

 

[Nlhicks]

I got this error:

 

[Nlhicks]

I reorganized things to look just like yours above and got this error:

 

[Nlhicks]

 

[HaHoBe]

Hi Nicole,

is it too hard to copy the code instead of placing typos in it?

Instead of

       .Font.Bold - True

it`s

       .Font.Bold = True

Ciao,
Holger

 

[Nlhicks]

Hi Nicole,

is it too hard to copy the code instead of placing typos in it?

Instead of

       .Font.Bold - True

it`s

       .Font.Bold = True

Ciao,
Holger