Excel VBA Regex: Format cell to Bold if starts with three spaces

Question

I am trying to scan through column "A" and apply the bold font to all cells which start with three spaces and then continue with any other characters. If a cell string starts with four space characters, or more; the bold font should not be applied to it.

This is what I have up to now; which causes all the cells of column A to become bold.

Sub Macro1() 

Dim regEx As New RegExp

NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
regEx.Pattern = "(\s{3})(\S)"
regEx.Global = False

Sheets("1022_CPU").Activate
Range("A2").Activate
Application.ScreenUpdating = False

Do Until IsEmpty(ActiveCell)
    If regEx.Test(ActiveCell.Value) Then
        ActiveCell.Font.Bold = True
    Else
        ActiveCell.Font.Bold = False
    End If
    ActiveCell.Offset(1, 0).Activate

Loop

Range("A1").Select
Application.ScreenUpdating = True

End Sub

EDIT : Sample Data:

   org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process()
    org.apache.coyote.http11.Http11Processor.process()
     org.apache.catalina.connector.CoyoteAdapter.service()
      org.apache.catalina.core.StandardEngineValve.invoke()

Taking into consideration the above data I would like to apply bold font on the first line (three spaces in front of "org." but not on the other three lines. This is why build in functions such as the "LEFT" function will not work; as it will apply the bold font on all lines.

Please [avoid using activate](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) in your code. The `Applicaiton.ScreenUpdating` aren't very necessary if you just use indexes. — cheezsteak, Nov 26 '14 at 17:47

Mathieu Guindon · Accepted Answer · 2014-11-26T20:50:41.530

which start with three spaces and then continue with any other characters.

I think this regex pattern would match your criteria:

regEx.Pattern = "^\s{3}.*$"

Whether all cells in column A should or shouldn't be bold is hard to tell without a bit of sample data.

As an aside, you don't need that If block:

If regEx.Test(ActiveCell.Value) Then
    ActiveCell.Font.Bold = True
Else
    ActiveCell.Font.Bold = False
End If

regEx.Test(ActiveCell.Value) is a Boolean, and so is ActiveCell.Font.Bold - therefore you can just assign it to the result of the regEx.Test call:

ActiveCell.Font.Bold = regEx.Test(ActiveCell.Value)

That won't fix your issue, but certainly looks cleaner ;)

That said, I don't think you need VBA, even less a RegEx to do this. Try a conditional format: select column A, and then use a formula to determine format: =LEFT(A1,3)=" ", and make it bold.

Taking into consideration the sample data provided, it appears you have analyzed/worded the problem statement wrong:

which start with three spaces and then continue with ~~any other characters~~ non-whitespace characters.

If the data is always going to be at least 4 characters long, I don't see why the conditional format approach wouldn't work:

=AND(LEFT(A1,3)="   ",MID(A1,4,1)<>" ")

Would bold rows where the first 3 characters are spaces and the 4th character isn't a space.

Hi @retailcoder. Thanks for your reply. Please see edit on original question which takes into consideration your answer and comments. — Stelios, Nov 26 '14 at 20:45
@Stelios edited; I'd still do that with a conditional formatting formula ;) — Mathieu Guindon, Nov 26 '14 at 20:51

Chrismas007 · Answer 2 · 2014-11-28T14:53:45.940

0

If you want to avoid RegEx try this: (It Trim's the cells which deletes all the leading and trailing spaces. If the length + 3 for 3 spaces matches the original length, then it turns the cell bold)

Sub Macro1() 

Dim LastRow as Long
Dim CurRow As Long

Sheets("1022_CPU").Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False

For CurRow = 2 to LastRow
    If Len(Cells(CurRow, 1).Value) = Len(Trim(Cells(CurRow, 1).Value))+3
        Cells(CurRow, 1).Font.Bold = True
    Else
        Cells(CurRow, 1).Font.Bold = False
    End If
Next CurRow

Range("A1").Select
Application.ScreenUpdating = True

End Sub

edited Nov 28 '14 at 14:53

answered Nov 26 '14 at 17:38

Chrismas007

6,085
4
24
47

What you have said makes sense but the result does not seem to be what is expected. Don't know why. I copy paste your code (added a "Then" for the if statement) but still the result is not as expected - it applies bold on all rows. – Stelios Nov 26 '14 at 21:21
Try now Stelios. Updated to reflect cell references and not "ActiveCell". – Chrismas007 Nov 28 '14 at 15:08

Excel VBA Regex: Format cell to Bold if starts with three spaces

2 Answers2