Complex conditional text formatting in Excel

Question

I need to conditionally bold text inside of an Excel spreadsheet. The logic is as follows:

For each record, I need to bold the last name first initial in column E that corresponds with the name of the person in column B (which is formatted Last Name, First Name). All the last name first initial are separated by commas in column E. Note that there might be two of the same last name with a different first initial in column E. I need to iterate through a spreadsheet with 1,000+ records. Example below:

A	B	C	D	E
value	Smith, Joseph	value	value	Jones K, Jenkins T, Smith J, Hines L, Abdhel B, Higgins M
value	Roberts, Anna	value	value	Taylor B, Starbert K, Helmann E, Santoro P, Stebnitz M, Hamilton A, Brown P, Palmer A, Roberts A, Stanton J
value	Chen, Jennifer	value	value	Anderson B, Chen J, Flanders C, Chen P, Aberdeen T, Daniels P

For the first record, "Smith J" in column E must be made bold based on the "Smith, Joseph" in column B.
For the second record, "Roberts A" in column E must be made bold based on the "Roberts, Anna" in column B.
For the thrid record, "Chen J" must be made bold base on the "Chen, Jennifer" in column B -- PLEASE NOTE that there is another "Chen" ("Chen P"), in column E that must not be made bold.

I greatly appreciate any help.

user_13702221 · Answer

Bianca,

Welcome to StackOverflow.

I can not figure out a way to do this with formulas in the UI Conditional Formatting.

However, the following macro will accomplish the task.

Option Explicit

Sub BoldPartString()

   Dim lRowCntr   As Long
   Dim iSStrLen   As Integer
   Dim iStart     As Integer
   Dim zSearchStr As String
   
   lRowCntr = 1
   
   
   Do
   
     'Determine the part of the Col B value to use to search Col E
     zSearchStr = Left(Cells(lRowCntr, "B"), InStr(Cells(lRowCntr, "B"), ",") - 1) & _
                   Mid(Cells(lRowCntr, "B"), InStr(Cells(lRowCntr, "B"), ",") + 1, 2)
                   
     'Find the number of characters you'll have to format once the string is found in Col E
     iSStrLen = Len(zSearchStr)
     
     'Search Col E for the value in zSearchStr
     If (InStr(Cells(lRowCntr, "E"), zSearchStr) > 0) Then
     
       'Find the starting character location in Col E
       iStart = InStr(Cells(lRowCntr, "E"), zSearchStr)
       
       'Apply the Bold FontStyle to the substring using previously calculated Start/Stop values
       Cells(lRowCntr, "E").Characters(iStart, iSStrLen).Font.FontStyle = "Bold"
     
     End If
   
     'Move to next row
     lRowCntr = lRowCntr + 1  'Next Row
     
   Loop Until Cells(lRowCntr, "A") = ""
   
End Sub

Complex conditional text formatting in Excel

1 Answers