Find Text, Add Border 6 Columns to Right
 

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 doesnt work (or I wouldnt be posting here). It fails
on this line:
If Selection = "*Total" Then

What am I doing wrong?

Regards,
Ryan---


--
RyGuy

Find Text, Add Border 6 Columns to Right
 

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

"ryguy7272" wrote:

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 doesnt work (or I wouldnt be posting here). It fails
on this line:
If Selection = "*Total" Then

What am I doing wrong?

Regards,
Ryan---


--
RyGuy

Find Text, Add Border 6 Columns to Right
 

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


"ryguy7272" wrote in message
...
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 doesnt work (or I wouldnt be posting here). It
fails
on this line:
If Selection = "*Total" Then

What am I doing wrong?

Regards,
Ryan---


--
RyGuy

Find Text, Add Border 6 Columns to Right
 

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

"ryguy7272" wrote:

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 doesnt work (or I wouldnt be posting here). It fails
on this line:
If Selection = "*Total" Then

What am I doing wrong?

Regards,
Ryan---


--
RyGuy

Find Text, Add Border 6 Columns to Right
 

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ryguy7272" wrote in message
...
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 doesnt work (or I wouldnt be posting here). It
fails
on this line:
If Selection = "*Total" Then

What am I doing wrong?

Regards,
Ryan---


--
RyGuy

Find Text, Add Border 6 Columns to Right
 

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--

--
RyGuy


"Mike H" wrote:

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

"ryguy7272" wrote:

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 doesnt work (or I wouldnt be posting here). It fails
on this line:
If Selection = "*Total" Then

What am I doing wrong?

Regards,
Ryan---


--
RyGuy

Find Text, Add Border 6 Columns to Right
 

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

If C.Value Like "*Total" Then

Mike

"ryguy7272" wrote:

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--

--
RyGuy


"Mike H" wrote:

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

"ryguy7272" wrote:

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 doesnt work (or I wouldnt be posting here). It fails
on this line:
If Selection = "*Total" Then

What am I doing wrong?

Regards,
Ryan---


--
RyGuy

Find Text, Add Border 6 Columns to Right
 

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

Find Text, Add Border 6 Columns to Right
 

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"ryguy7272" wrote in message
...
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 doesnt work (or I wouldnt be posting here). It
fails
on this line:
If Selection = "*Total" Then

What am I doing wrong?

Regards,
Ryan---


--
RyGuy

Find Text, Add Border 6 Columns to Right
 

Or mine which doesn't care

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike H" wrote in message
...
Go with Rick's solution but change the if line to include a wildcard

If C.Value Like "*Total" Then

Mike

"ryguy7272" wrote:

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--

--
RyGuy


"Mike H" wrote:

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

"ryguy7272" wrote:

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 doesnt work (or I wouldnt be posting here). It
fails
on this line:
If Selection = "*Total" Then

What am I doing wrong?

Regards,
Ryan---


--
RyGuy

Find Text, Add Border 6 Columns to Right
 

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--

--
RyGuy


"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ryguy7272" wrote in message
...
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 doesnt work (or I wouldnt be posting here). It
fails
on this line:
If Selection = "*Total" Then

What am I doing wrong?

Regards,
Ryan---


--
RyGuy