Capture values and assign them to variables in Excel VBA

Hello

Although fellows Experts have answered your question correctly, I have thought of your issue differently especially that I have participated in previous question and know your intention in this question and where you want to end up with.

I am being straight forward in my reply and maybe I am completely off target only you can confirm that.

Here is my proposal for a possibility to be able to change fonts and size and color of several items within a specified text.

1) In col A you input your text as is:
2) Col B the macro will provide the final output based on:
3) You have variable columns that goes from Col C to Col U (19 different possible variables)
4) you simply input in each column starting C the word or group of text that you want to change and formatted the way you like.
5) Columns C to U are all VALIDATE ie you cannot input text that does not exist in the string of Col A ie you cannot input words or group of text that is not found in other words you need to have words that really exist there.
6) Once formatting is completed then launch the macro by activating the green button and check results in Col B.

For your easy convenience here is the code for that

Option Explicit

Sub FormatInside()
Dim WS As Worksheet
Dim MaxRow As Long, MaxCol As Long
Dim I As Long, J As Long
Dim sOrig As String, sText As String, sReplace As String
Dim istart As Integer, itot As Integer


'---> Disable Events
With Application
    .EnableEvents = False
    .DisplayAlerts = False
'    .ScreenUpdating = False
End With

'---> Set Variables
Set WS = ActiveSheet
MaxRow = WS.UsedRange.SpecialCells(xlCellTypeLastCell).Row
MaxCol = WS.UsedRange.SpecialCells(xlCellTypeLastCell).Column
WS.Range("B2:B" & MaxRow).ClearContents
WS.Range("B2:B" & MaxRow).ClearFormats

'---> Start Process
For I = 2 To MaxRow
    If WS.Cells(I, "A") <> "" Then
        sOrig = WS.Cells(I, "A")
        WS.Cells(I, "B") = sOrig
        
        For J = 3 To MaxCol
            If WS.Cells(I, J) <> "" Then
                '---> Spot Start and total length for the variable
                istart = InStr(1, WS.Cells(I, "A"), WS.Cells(I, J), vbTextCompare)
                itot = Len(WS.Cells(I, J))
                sText = WS.Range("B" & I).Characters(istart, itot).Text
                sReplace = WS.Cells(I, J)
                
                '---> Take a copy of the Item
                WS.Range("B" & I).Characters(istart, itot).Text = sReplace
                
                WS.Range("B" & I).Characters(istart, itot).Font.Background = WS.Cells(I, J).Font.Background
                WS.Range("B" & I).Characters(istart, itot).Font.Bold = WS.Cells(I, J).Font.Bold
                WS.Range("B" & I).Characters(istart, itot).Font.Color = WS.Cells(I, J).Font.Color
                WS.Range("B" & I).Characters(istart, itot).Font.FontStyle = WS.Cells(I, J).Font.FontStyle
                WS.Range("B" & I).Characters(istart, itot).Font.Italic = WS.Cells(I, J).Font.Italic
                WS.Range("B" & I).Characters(istart, itot).Font.Name = WS.Cells(I, J).Font.Name
                WS.Range("B" & I).Characters(istart, itot).Font.Size = WS.Cells(I, J).Font.Size
                WS.Range("B" & I).Characters(istart, itot).Font.Strikethrough = WS.Cells(I, J).Font.Strikethrough
                WS.Range("B" & I).Characters(istart, itot).Font.Subscript = WS.Cells(I, J).Font.Subscript
                WS.Range("B" & I).Characters(istart, itot).Font.Superscript = WS.Cells(I, J).Font.Superscript
                
                WS.Range("B" & I).Characters(istart, itot).Font.ThemeFont = WS.Cells(I, J).Font.ThemeFont
                WS.Range("B" & I).Characters(istart, itot).Font.TintAndShade = WS.Cells(I, J).Font.TintAndShade
                WS.Range("B" & I).Characters(istart, itot).Font.Underline = WS.Cells(I, J).Font.Underline
            End If
        
        Next J
    End If
Next I

WS.Range(WS.Range("B:B"), WS.Columns(MaxCol)).EntireColumn.AutoFit

'---> Enable Events
With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

'---> Advise User
MsgBox "Text formated created in Col B", vbInformation, "Format Cells"

End Sub

Select allOpen in new window

Attached is the workbook
Let me know your comments.
gowflow
Format-Inside-V01.xlsm

Thanks for the comments and code examples.

In the interest of fairness and full disclosure, I think it is appropriate to post some information regarding the background of this thread and also the following statement:

Expert Comment by: gowflow
I have participated in previous question and know your intention in this question and where you want to end up with.

That is an accurate statement and, as noted, my question in this thread is one more in a series of questions I have recently posted on the topic of VBA coding. However, my OP does not mention previous threads and was purposely worded to address a single, specific issue. Therefore, since the first and second Expert responses do provide answers to my original question, I believe it is appropriate to award points to those two contributors and close the thread.

Regarding the third Expert response by gowflow:

Your code is fantastic and has already opened my eyes to some additional capabilities I was unaware of in VBA. Thank you very much for the time and effort that obviously went into writing the code and posting your reply.

I have some additional thoughts and questions to communicate but, because your post reaches well beyond the topic of this particular thread and in order to award points to all who have made valuable contributions, I propose that I close this thread as previously explained then open a new one where you can repost your comments and code just as they appear here.

To all three Experts: thank you again for your contributions and please feel free to communicate any thoughts or questions regarding my proposal either below or in a personal message.

Steve

I am fine with this anyway it turns no problem for me. As I mentioned I was aware that my solution was beyond and outside the scope of this question but had to put it coz I am usually straightforward person and favor finding solutions to getting points accumulated.

Regards
gowflow

Thank you for the helpful comments.

You're welcome Steve!