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

[bbalch]

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

 

[mikerickson]

This could be done with Conditional Formatting and the condition

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

 

[bbalch]

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]

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]

Here's a macro in case you are adamant about not using CF.

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]

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