VBA: to bold text if the text starts with" ["

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
55
I'm trying to create a macro that will bold all text on a row if the text in column A starts with " [" then move down to the next row and repeat. Any suggestions?

Example:

Column A Column B Column C
[501650 Dues] 123 456
Billboard 1 2
WSJ 2 3
[501660 Subs] 500 1000
MRI 1 2
ASA 2 2
ARF 3 4
[507050 F&B] 3 5
[501150 MISC] 7 8
[509500 Parking] 5 4
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,559
This could be done with Conditional Formatting and the condition

=(LEFT($A1,1)="[")
 

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
55
The conditional format will work to highlight the text in cell A1 if it starts with "[".

I have approximately 80 individual reports with 500 rows of data and text in columns A through S that need to be formatted if the start with "[". So what I'm after is vba for an if statement that would do the left formula and then bold the data in columns A - S if the first characters of the left function are equal to "[".

Something like =if(left($A1,3)="[''),"Select the data in that row in columns A - S and apply bold","no change") then move to the next row and repeat
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,559
Conditional formatting will highlight all the cells in a row if the cell in column A of that row starts with "["

The condition that I used has a relative row reference and absolute column reference.

If you put that CF formula on a cell in Row 1, it will respond to the state of the contents of column A.
If you copy that CF to all the cells in Row 1, they will respond to that same cell
If you copy that CF to all the cells in a workbook, each of them will respond the the text in column A of their respective rows.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,846
Office Version
2010
Platform
Windows
Here's a macro in case you are adamant about not using CF.
Code:
Sub MakeBold()
Dim V As Variant, R As Range
V = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
Application.ScreenUpdating = False
For i = LBound(V, 1) To UBound(V, 1)
    If Left(Trim(V(i, 1)), 1) = "[" Then
        If R Is Nothing Then
            Set R = Cells(i, 1).Resize(1, 19)
        Else
            Set R = Union(R, Cells(i, 1).Resize(1, 19))
        End If
    End If
Next i
If Not R Is Nothing Then R.Font.Bold = True
Application.ScreenUpdating = True
End Sub
 

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
55
I caught up to you on the conditional formatting. I added the left formula into my macro and that did the trick. Thank you both!

I used

Range("A8").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(LEFT($A8,3)="" ["")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.Copy
Range("A9:S500").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
 

Watch MrExcel Video

Forum statistics

Threads
1,099,504
Messages
5,469,003
Members
406,627
Latest member
IncandenzaH1997

This Week's Hot Topics

Top