Automatic Partial formatting - all text in brackets in cells on a worksheet

[XL123]

Hi,
I have not found an answer for this. In my EXCEL spreadsheet I’d like to automatically reformat the characters between the quotes from this “(X)” to this “(X)”.In other words, whenever I type characters that start and end with parentheses, I’d like EXCEL to automatically reformat the parentheses and enclosed characters into a BOLD + ITALIC format.


So, for example, when I type this:

(Gm) Know I’ve done wrong, (Bb) left your heart torn​

<colgroup><col width="488" style="width: 488pt;"></colgroup><tbody> </tbody>

I automatically get this:

(Gm) Know I’ve done wrong, (Bb) left your heart torn I tried conditional formatting with search / Find formulas & Find and replace. but these standard Excel features don't work. They bold and italise the full cell. Is a piece of macro code required? Thanks for your reply​

<colgroup><col width="488" style="width: 488pt;"></colgroup><tbody> </tbody>

 

[ryansclev]

I am sure there is a prettier way to do this, but this will work if you put the code in the worksheet's code that you are typing into. Everytime a cell is changed it will search through the string that is typed and look for the parenthesis and bold the string within and including the parenthesis. Again, this will evaluate whenever you change any cell on the sheet. If you are only trying to achieve this on one column, say column A, just insert an if statement

If Target.Column = 1 Then

so that it will only evaluate the strings in column a.

Sub Worksheet_Change(ByVal Target As Range)
Dim strCellValue As String
Dim strStringPart As String
Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim booCountCharacters As Boolean
Dim intCellLength As Integer
'Reset Bold & Itallic font in case edits are being made
Target.Font.Bold = False
Target.Font.Italic = False
strCellValue = Target
intCellLength = Len(strCellValue)
x = 1
For i = 1 To intCellLength
    strStringPart = Mid(strCellValue, i, 1)
    If strStringPart = "(" Then
        booCountCharacters = True
        y = i
    Else
        If strStringPart = ")" Then
            'bold the bumber of characters since (
            Target.Characters(y, x).Font.Bold = True
            Target.Characters(y, x).Font.Italic = True
            booCountCharacters = False
            x = 1
        End If
    End If
    If booCountCharacters = True Then
        x = x + 1
    End If
Next
End Sub

 

[XL123]

Thanks alot. This appears to work fine. Much appreciated.

 

[hiker95]

XL123,

whenever I type characters that start and end with parentheses, I’d like EXCEL to automatically reformat the parentheses and enclosed characters into a BOLD + ITALIC format

Sorry, I missed the above part.

Below is a macro that will do what you want for all the text strings in column A.


The below macro code has been modified from a macro by Rick Rothstein, MrExcel MVP.


Sample raw data:

Excel 2007
	A
1	(Gm) Know Ive done wrong, (Bb) left your heart torn
2	(XL123) Know Ive done wrong, (XL123) left your heart torn
3	(hiker95) Know Ive done wrong, (hiker95) left your heart torn
4	Know (Gm) Ive done wrong, left (Gm) your heart torn
5	Know Ive (XL123) done wrong, left your (XL123) heart torn
6	Know Ive done (hiker95) wrong, left your heart (hiker95) torn
7	(Gm) Know Ive done wrong, (Bb) left your heart torn
8	(XL123) Know Ive done wrong, (XL123) left your heart torn
9	(hiker95) Know Ive done wrong, (hiker95) left your heart torn
10	Know (Gm) Ive done wrong, left (Gm) your heart torn
11	Know Ive (XL123) done wrong, left your (XL123) heart torn
12	Know Ive done (hiker95) wrong, left your heart (hiker95) torn
13	They bold and italise (Gm) the full cell. Is a piece of macro code required?
14
Sheet1

The HTML Maker will not display BOLD and Italic correct, so, what follows is the text from above, modified:

(Gm) Know I’ve done wrong, (Bb) left your heart torn
(XL123) Know I’ve done wrong, (XL123) left your heart torn
(hiker95) Know I’ve done wrong, (hiker95) left your heart torn
Know (Gm) I’ve done wrong, left (Gm) your heart torn
Know I’ve (XL123) done wrong, left your (XL123) heart torn
Know I’ve done (hiker95) wrong, left your heart (hiker95) torn
(Gm) Know I’ve done wrong, (Bb) left your heart torn
(XL123) Know I’ve done wrong, (XL123) left your heart torn
(hiker95) Know I’ve done wrong, (hiker95) left your heart torn
Know (Gm) I’ve done wrong, left (Gm) your heart torn
Know I’ve (XL123) done wrong, left your (XL123) heart torn
Know I’ve done (hiker95) wrong, left your heart (hiker95) torn
They bold and italise (Gm) the full cell. Is a piece of macro code required?


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub FormatTextInBracketsBoldItalic()
' hiker95, 08/04/2014, ME796343
Dim c As Range, s, i As Long, Position As Long
For Each c In Range("A1", Cells(Rows.Count, "A").End(xlUp))
  If InStr(c, "(") Then   'And InStr(c, ")") Then
    s = Split(c, " ")
    For i = LBound(s) To UBound(s)
      Position = InStr(1, c.Value, s(i), vbTextCompare)
      If Left(s(i), 1) = "(" And Right(s(i), 1) = ")" Then
        Do While Position
          With c.Characters(Position, Len(s(i))).Font
            .FontStyle = "Bold Italic"
          End With
          Position = InStr(Position + 1, c.Value, s(i), vbTextCompare)
        Loop
      End If
    Next i
  End If
Next c
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FormatTextInBracketsBoldItalic macro.

 

[XL123]

Thank Hiker much appreciate & very grateful

 

[hiker95]

XL123,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[dor1angray]

Hi Hiker!

Would it be too much to ask you to modify your macros to get all text in [text], and in <text> to be formatted in red color within all cells of the given sheet? I have been trying to modify your macros for hours so that it worked for these conditions but all in vain. Basically, I need all HTML tags and some variables enclosed into square brackets that appear in the spreadsheet, e.g. [variables] and <tags> be in color (red) to identify them as something that must not be edited.

I do appreciate if you could help me out with this.

Thank you!

 

[dor1angray]

The

[COLOR=#ff0000]<text>[/COLOR]

were deleted at the time of posting. Those need to be formatted in red too. So, basically all text in

[COLOR=#ff0000]<>[/COLOR]

and [] needs to be turned into red color.

Thank you in advance for your help with this!

 

[hiker95]

dor1angray,

I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the actual raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

 

[hiker95]

dor1angray,

If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com