Find Text, Add Border 6 Columns to Right

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ryguy7272 - 25 Jan 2008 15:59 GMT

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

Signature

RyGuy

Reply to this Message

Mike H - 25 Jan 2008 16:15 GMT

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

> 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
[quoted text clipped - 24 lines]
> Regards,
> Ryan---

Reply to this Message

ryguy7272 - 25 Jan 2008 16:28 GMT

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

Signature

RyGuy

> Try this
>
[quoted text clipped - 49 lines]
> > Regards,
> > Ryan---

Reply to this Message

Mike H - 25 Jan 2008 16:35 GMT

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

If C.Value Like "*Total" Then

Mike

> 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
[quoted text clipped - 66 lines]
> > > Regards,
> > > Ryan---

Reply to this Message

Don Guillett - 25 Jan 2008 16:38 GMT

Or mine which doesn't care

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Go with Rick's solution but change the if line to include a wildcard
>
[quoted text clipped - 82 lines]
>> > > Regards,
>> > > Ryan---

Reply to this Message

Rick Rothstein (MVP - VB) - 25 Jan 2008 16:36 GMT

> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 25 Jan 2008 16:21 GMT

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

>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
[quoted text clipped - 26 lines]
> Regards,
> Ryan---

Reply to this Message

Rick Rothstein (MVP - VB) - 25 Jan 2008 16:38 GMT

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

> Are you looking for something like this...
>
[quoted text clipped - 41 lines]
>> Regards,
>> Ryan---

Reply to this Message

Mike H - 25 Jan 2008 16:24 GMT

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

Reply to this Message

Don Guillett - 25 Jan 2008 16:26 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

Reply to this Message

ryguy7272 - 25 Jan 2008 16:51 GMT

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

Signature

RyGuy

> Sub doborders()' for ONE
> Columns("J").Find("Total").Offset(, 1).Resize(, 6) _
[quoted text clipped - 44 lines]
> > Regards,
> > Ryan---

Reply to this Message