Find Text, Add Border 6 Columns to Right

[ryguy7272]

I found this tiny macro on the web and I was trying to modify it for my
purposes, which basically includes finding and selecting cells with the word
‘Total’ and placing a boarder around cells in that same row, STARTING one
column to the right and six columns to the right of that.

Sub AddBorders()
'start cell
Range("J1:J500").Select
Do Until ActiveCell = Empty
If Cells = "*Total" Then
Selection.Offset(0, 1).Select
ActiveCell.Offset(0, 6).Select
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
End If
Loop
End Sub

Obviously the macro doesn’t work (or I wouldn’t be posting here). It fails
on this line:
If Selection = "*Total" Then

What am I doing wrong?

Regards,
Ryan---

 

[Mike H]

Try this

Sub AddBorders()
'start cell
Set myrange = Range("J1:J500")
For Each c In myrange
If c.Value = "Total" Then
With c.Offset(0, 1)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
With c.Offset(0, 6)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
End If
Next
End Sub

Mike

 

[Rick Rothstein \(MVP - VB\)]

Are you looking for something like this...

Sub AddBorders()
Dim C As Range
Dim SixCells As Range
For Each C In ActiveSheet.Range("J1:J500")
If C.Value = "Total" Then
Range(C.Offset(0, 1), C.Offset(0, 6)).Cells. _
Borders.LineStyle = xlContinuous
End If
Next
End Sub

Rick

 

[Mike H]

Just spotted the wildcard and if it was deliberate and not a typo change
If c.Value = "Total" Then
to
If c.Value Like "*Total" Then

The ammended line will evalute as True for and string in the cell that ends
with the word total. "*Total*" picks up and string with the word total
anywhere within it

Mike

 

[Don Guillett]

Sub doborders()' for ONE
Columns("J").Find("Total").Offset(, 1).Resize(, 6) _
.BorderAround , Weight:=xlMedium
End Sub

Sub dobordersmore()' For many
With ActiveSheet.Columns("j")
Set c = .Find("Total", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 1).Resize(, 6) _
.BorderAround , Weight:=xlMedium
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

 

[ryguy7272]

Thanks Mike! I appreciate the effort, but there are two open issues. Excel
can't seem to find the cells with Total in them. I have things like B Total
and C Total, so I tried this:
If c.Value = "*Total" Then

However, that doesn't work...nothing happens. Also, If I make a small
modification, such as :
If c.Value = "B Total" Then

The cell immediately to the right has a border and the cell 6 to the right
has a border, but I was hoping to put a border on all cells in that row, one
to the right all the way through six to the right. There is probably an easy
solution that I'm not aware of. Any thoughts? I'll play with what I have
now and try to get it to work.

Ryan--

 

[Mike H]

Go with Rick's solution but change the if line to include a wildcard

If C.Value Like "*Total" Then

Mike

 

[Rick Rothstein \(MVP - VB\)]

Thanks Mike! I appreciate the effort, but there are two open issues.

Excel
can't seem to find the cells with Total in them. I have things like B
Total
and C Total, so I tried this:
If c.Value = "*Total" Then

You need to use this...

If c.Value Like "*Total" Then

assuming the word "Total" make up the last characters in your cell;
otherwise add an asterisk after the word Total also.

Here is the routine I posted, modified for this...

Sub AddBorders()
Dim C As Range
Dim SixCells As Range
For Each C In ActiveSheet.Range("J1:J500")
If C.Value Like "*Total*" Then
Range(C.Offset(0, 1), C.Offset(0, 6)).Cells. _
Borders.LineStyle = xlContinuous
End If
Next
End Sub

Rick

 

[Rick Rothstein \(MVP - VB\)]

As I now see, you want the word "Total" to be part of the cells content, not
its entire content. This revised code should do what you want...

Sub AddBorders()
Dim C As Range
Dim SixCells As Range
For Each C In ActiveSheet.Range("J1:J500")
If C.Value Like "*Total*" Then
Range(C.Offset(0, 1), C.Offset(0, 6)).Cells. _
Borders.LineStyle = xlContinuous
End If
Next
End Sub

Rick

 

[Don Guillett]

Or mine which doesn't care

 

[ryguy7272]

Dang, you guys make it look so easy! I now remember about the difference
between these:
= "*Total"

and

Like "*Total"

I've used this technique before, but temporarily forgot this time. However,
I wasn't going to figure out this one:
Range(C.Offset(0, 1), C.Offset(0, 6)).Cells. _
Borders.LineStyle = xlContinuous

or this one:
c.Offset(, 1).Resize(, 6) _
..BorderAround , Weight:=xlMedium


So thanks for that stuff guys!!

Regards,
Ryan--