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 can't see where those values come from that code. Suggest you use Instr function: If string contains "Downrate" make .Font.Color property of the range to be red. If Instr finds Uprate, make font green. You can use RGB function but vb constants are easier if you can use standard colours (e.g. .Font.Color = vbRed)

 

[Nlhicks]

Hiya,

It is concatenating data from this table

 

[Nlhicks]

Cancelled this message

 

[Nlhicks]

Deleted

 

[Micron]

That doesn't identify what code line makes that happen - at least not for me.

I can't see where those values come from that code.

It's why I didn't provide a code sample. No idea where to put it.

 

[HaHoBe]

Hi Nicole,

instead of

  With wksWork
    .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

try

    With wksWork
      With .Range("D32")
        .Value = ("(" & .Range("L11") & " " & .Range("K13") & " " & .Range("L13") & " " & .Range("Q13") & " " & _
            "," & " " & .Range("O11") & " " & .Range("N13") & " " & .Range("O13") & " " & .Range("Q13") & ")")
        If InStr(1, LCase(.Value), "downrate") > 0 Then
          .Font.ColorIndex = 3
        ElseIf InStr(1, LCase(.Value), "uprate") > 0 Then
          .Font.ColorIndex = 10
        Else
          .Font.ColorIndex = 1
        End If
        .Font.Bold = True
      End With
    End With

The target cell should get filled with the concatenation text as wanted, then a check is done of the lower case text to find either downrate (colour red), uprate (colour green) or none of the two (colour black).

Holger

P.S.: The thread title in the comments is misleading, should be

' https://www.mrexcel.com/board/threads/make-the-font-red-if-derate-or-and-green-if-uprate-or.1228931/

P.P.S.: you should try and give information about the columns and rows you refer to in the pictures.

 

[Nlhicks]

I am getting an error, Object does not support this property or method on the line
With .Range("D32")

 

[Nlhicks]

Also, all of the data I need for this code lives in the Master workbook at this time. I do not need to reference the Data File.

 

[Nlhicks]