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

 

[Nlhicks]

It is not changing colors and it is getting the wrong words. I have no idea where that is coming from

 

[Nlhicks]

For some reason I think it is looking at the code below the table and getting the values in there ( FROM Substation Owner TO Substation Owner MVA , Load Control Boundary tie? PRC-023-4 MVA). If you See in Column N is the From Substation Owner and column O is To Substation owner. Anyway it should not be getting those labels as the value it should be (Summer downrate -71 MVA, Winter Uprate +69 MVA)

 

[Nlhicks]

Got it:

Changed it to look like this:

 With wksWork
  
       .Range("D32").Value = ("(" & .Range("L11") & " " & .Range("K13") & " " & .Range("L13") & " " & .Range("Q13") & " " & _
       "," & .Range("O11") & " " & .Range("N13") & " " & .Range("O13") & " " & .Range("Q13") & ")")
    With .Range("D32")
        .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

 

[HaHoBe]

Hi Nicole,

I must apologize to post code that could not do what you want it to do. Introducing the second With just referred all other addresses for the concatenation to be relative to that cell instead of delivering the values of the cells wanted. So yes, the code could not work...

'additional variables needed in next part
    Dim varSplit          As Variant
    Dim lngSplit          As Long
    Dim lngStart          As Long
   
    With wksWork
      .Range("D32").Value = ("(" & .Range("L11") & " " & .Range("K13") & " " & .Range("L13") & " " & .Range("Q13") & " " & _
            "," & " " & .Range("O11") & " " & .Range("N13") & " " & .Range("O13") & " " & .Range("Q13") & ")")
      With .Range("D32")
        .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 = LBound(varSplit) Then
            lngStart = 2
          Else
            lngStart = InStr(1, .Value, ",") + 1
          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

Again, I must apologize for changing working code into non-working code, taking so much time to realize the reason for this and correcting.

Glad to see that you could fix it on your own-.

Holger