VBA Code for formatting to stop when it reaches a WRAP text range

[peter8848]

Hi All,

I developed a VBA code for formatting my excel reports say from Column B to D, F to G to change the font. color etc. As this stage I am doing a certain range say from Row 11 to 195 to avoid any Wrap text area as if i just select as Range("B:D,F:G").Select, then it will format more columns and rows than i needed to cause messy reporting format. Is there anyway to write a VBA code to stop the formatting to a particular row before reaching a wrap text area? So in my example the wrap text happened in Row 196 which I need to manually change my code. thanks a lot


Range("B11").Select
Range("B11:D195,F11:G195").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"

 

[VBE313]

Not to sure how you want to loop through these columns but here is a code that will stop the code with a message box saying "This cell has Wrap Text" and the cell address.

Sub Macro1()
Dim lastRow As Long, cl As Object, strCells As String
With Sheets("Sheet1")
    lastRow = .Cells(.Rows.Count, "G").End(xlUp).Row + 1
    strCells = "B11:B" & lastRow
    For Each cl In .Range(strCells)
        If cl.WrapText = True Then
            MsgBox "This cell has Wrap Text " & cl.Address
            Exit Sub
        End If
    Next cl
End With
End Sub

 

[peter8848]

Not to sure how you want to loop through these columns but here is a code that will stop the code with a message box saying "This cell has Wrap Text" and the cell address.

Sub Macro1()
Dim lastRow As Long, cl As Object, strCells As String
With Sheets("Sheet1")
    lastRow = .Cells(.Rows.Count, "G").End(xlUp).Row + 1
    strCells = "B11:B" & lastRow
    For Each cl In .Range(strCells)
        If cl.WrapText = True Then
            MsgBox "This cell has Wrap Text " & cl.Address
            Exit Sub
        End If
    Next cl
End With
End Sub

Hi VBE313,

Thanks a lot for your reply. Sorry how would the best way to group your code into mine for the formatting part?

Cheers,

Peter