find multiple instances and select entire rows

[MikeF]

Would like to do some simple formatting on only rows in one specific subtotal
level.
The code should effictively find all instances where the word "total" occurs
in b6.b150 , select the row, bold the contents, and apply some borders.
The following code works, but only on the first occurence.
Is there any way to select all of, and only those rows that contain "total"?


ActiveSheet.Outline.ShowLevels RowLevels:=3

Range("b6.b150").Select
Cells.Find(What:=Right("Total", 5)).Select
Selection.EntireRow.Select
With Selection
.Font.Bold = True
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Borders(xlEdgeBottom).Weight = xlThick
End With

 

[Mike H]

Hi,

Try this

Sub Stantial()
Dim MyRange As Range
For Each c In Range("B6:B150")
If UCase(c.Value) = "TOTAL" Then
If MyRange Is Nothing Then
Set MyRange = c.EntireRow
Else
Set MyRange = Union(MyRange, c.EntireRow)
End If
End If
Next
If Not MyRange Is Nothing Then
With MyRange
.Font.Bold = True
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Borders(xlEdgeBottom).Weight = xlThick
End With
End If
End Sub

Mike

 

[MikeF]

Mike,
Thanx for the reply, but ... it doesn't work.

Perhaps because the text contains the word Total, in lower case, with other
text to the left. But nothing happens when the macro is run.
*** The Sub Stantial part is fun !!

Also, my I erred in my original msg, where the formatting should be done
only on columns B thru L for each row that contains the text "Total".

Regards,
- Mike

 

[Mike H]

Hi,

Yes it would have helped had you mentioned those two things , try this
instead

Sub Versive()
Dim MyRange As Range
For Each c In Range("B6:B150")
If InStr(UCase(c.Value), "TOTAL") Then
If MyRange Is Nothing Then
Set MyRange = c
Set MyRange1 = c.Resize(, 11)
Else
Set MyRange = Union(MyRange, c.Resize(, 11))
End If
End If
Next
If Not MyRange Is Nothing Then
With MyRange
.Font.Bold = True
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Borders(xlEdgeBottom).Weight = xlThick
End With
End If
End Sub

Mike

 

[Mike H]

OOps,

you don't need this line, it does no harm but delete it

Set MyRange = c


Mike

 

[MikeF]

Thanx Mike.
This actually does work, but with one strange anomaly ...
In the first occurence of "Total", which in the present scenario is at b7,
it only puts lines on column B.
Every other occurence it formats correctly.
????

*** Slightly revised code below, re line weight.


Sub Versive()
Dim MyRange As Range

ActiveSheet.Outline.ShowLevels RowLevels:=3

For Each c In Range("B6:B150")
If InStr(UCase(c.Value), "TOTAL") Then
If MyRange Is Nothing Then
Set MyRange = c
Set MyRange1 = c.Resize(, 11)
Else
Set MyRange = Union(MyRange, c.Resize(, 11))
End If
End If
Next
If Not MyRange Is Nothing Then
With MyRange
.Font.Bold = True
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With
End If

End Sub

 

[Mike H]

Hi,

There's a bug in the code

Set MyRange1 = c.Resize(, 11)

should be

Set MyRange = c.Resize(, 11)

Mike

 

[MikeF]

That did it, thanx again!!

Hi,

There's a bug in the code

Set MyRange1 = c.Resize(, 11)

should be

Set MyRange = c.Resize(, 11)

Mike

 

[Mike H]

Glad i could help

That did it, thanx again!!