Bold and red specific type of text in selected cell

evonne_j

New Member
Joined
Oct 29, 2011
Messages
4
I am writing a VBA to bold and red the text in a cell with the following condition

- any words insided "<" and ">" or "{" and "}" (e.g. "xyz <abc> xyz <123>" becomes "xyz <abc> xyz <123>" or "xyz {abc} xyz <123>" becomes "xyz {abc} xyz <123>"

the following code is what i try, it work normally, but when I test for the code with some strange string, for example, my selected cell with text starting by some strings which already in bold format, e.g. "a ""<abc>"" a" or "xyz <abc>", they will return "a <abc> a" (the bold format of first "a" disappear)
"xyz yz <abc>" (bold format of "xyz" disappear, "yz" added in between and "<abc>" did not turn red, the strangest thing is that "xyz <abc>" is still showing on formula bar which is different from what the cells value (xyz yz <abc>) display

Is there any bugs I did not notified? thanks.


Code:
Sub redbold()
    Dim cell As Range
    Dim pos, startpos As Long
    Dim targetref As Boolean
     
    For Each cell In Selection
         

        For pos = 1 To Len(cell.Value)

        If (Mid(cell.Value, pos, 1) = "<") Or (Mid(cell.Value, pos, 1) = "{") Then
            startpos = pos
            targetref = True

        ElseIf targetref And (Mid(cell.Value, pos, 1) = ">" Or Mid(cell.Value, pos, 1) = "}") Then
                With cell.Characters(startpos, pos - startpos + 1).Font
                    .ColorIndex = 3
                    .Bold = True
                End With
            targetref = False
        End If
        
        Next pos
        
    Next cell
End Sub
 
Last edited:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,194
Assuming Column A has input values you can try this code. Test this code in a new sheet.

You have to run Format_All macros,

Code:
Sub Format_All()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Call Format_BoldRed("<", ">")
Call Format_BoldRed("{", "}")
End Sub


Private Sub Format_BoldRed(Str1 As String, Str2 As String)
For i = 1 To Range("a" & Rows.Count).End(xlUp).Row
 StrtPos = InStr(Cells(i + 1, 1).Value, Str1)
 If StrtPos > 0 Then
    lnth = InStr(Cells(i + 1, 1).Value, Str2) - StrtPos + 1
    Cells(i + 1, 1).Select
    With ActiveCell.Characters(Start:=StrtPos, Length:=lnth).Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Color = -16776961
    End With
 End If
Next i
End Sub

Hope this helps!
 

evonne_j

New Member
Joined
Oct 29, 2011
Messages
4
Thank you vds for your good try, but I found that my topic issue still exist. will this be a problem of MS Office? because I am currently using Office 2010 and do not have other version.

My purpose of this code is to bold and red any text in selected cell which is bracket by "<" ">" or "{" "}" pairs and do not change the format of other text in the cell.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,898
Messages
5,627,530
Members
416,250
Latest member
darius_rebelo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top