Help With Offset Method

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
Hello,
Can anyone tell me the proper syntax to shorten the code below?
In other words:

Code:
 cell:cell.offset(0,36).Borders.ColorIndex=5

Any help is greatly appreciated.





Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cell In Range("a3:a1000")
If cell <> "" Then
With cell
  .Borders.ColorIndex = 5
  .Offset(0, 1).Borders.ColorIndex = 5
  .Offset(0, 2).Borders.ColorIndex = 5
  .Offset(0, 3).Borders.ColorIndex = 5
  .Offset(0, 4).Borders.ColorIndex = 5
  .Offset(0, 5).Borders.ColorIndex = 5
  .Offset(0, 6).Borders.ColorIndex = 5
  .Offset(0, 7).Borders.ColorIndex = 5
  .Offset(0, 8).Borders.ColorIndex = 5
  .Offset(0, 9).Borders.ColorIndex = 5
  .Offset(0, 10).Borders.ColorIndex = 5
  .Offset(0, 11).Borders.ColorIndex = 5
  .Offset(0, 12).Borders.ColorIndex = 5
  .Offset(0, 13).Borders.ColorIndex = 5
  .Offset(0, 14).Borders.ColorIndex = 5
  .Offset(0, 15).Borders.ColorIndex = 5
  .Offset(0, 16).Borders.ColorIndex = 5
  .Offset(0, 17).Borders.ColorIndex = 5
  .Offset(0, 18).Borders.ColorIndex = 5
  .Offset(0, 19).Borders.ColorIndex = 5
  .Offset(0, 20).Borders.ColorIndex = 5
  .Offset(0, 21).Borders.ColorIndex = 5
  .Offset(0, 22).Borders.ColorIndex = 5
  .Offset(0, 23).Borders.ColorIndex = 5
  .Offset(0, 24).Borders.ColorIndex = 5
  .Offset(0, 25).Borders.ColorIndex = 5
  .Offset(0, 26).Borders.ColorIndex = 5
  .Offset(0, 27).Borders.ColorIndex = 5
  .Offset(0, 28).Borders.ColorIndex = 5
  .Offset(0, 29).Borders.ColorIndex = 5
  .Offset(0, 30).Borders.ColorIndex = 5
  .Offset(0, 31).Borders.ColorIndex = 5
  .Offset(0, 32).Borders.ColorIndex = 5
  .Offset(0, 33).Borders.ColorIndex = 5
  .Offset(0, 34).Borders.ColorIndex = 5
  .Offset(0, 35).Borders.ColorIndex = 5
  .Offset(0, 36).Borders.ColorIndex = 5
  End With
 End If
 Next
End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Welcome to the Board!

Maybe something like this:

<font face=tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A3:A1000")
            <SPAN style="color:#00007F">If</SPAN> c <> "" <SPAN style="color:#00007F">Then</SPAN> Range(c, c.Offset(, 36)).Borders.ColorIndex = 5
        <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note that I use "c" instead of "cell" as it's just to **** close to "cells", so it helps to eliminate some confusion.

Hope that helps,

Smitty

EDIT:
Code:
cell:cell.offset(0,36).Borders.ColorIndex=5
You almost had it. ;)
 
Upvote 0
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each oCell In Range("a3:a1000")
If oCell <> "" Then
    With Range(Cells(oCell.Row, 1), Cells(oCell.Row, 37))
     .Borders.ColorIndex = 5
    End With
 End If
 Next
End Sub

HOWEVER... You may want to do this , instead, with one formula in "Conditional Format" . Conditional format , unlike a macro, will work even if the user does not have macro's enabled.
 
Upvote 0
Hello Macropheliac, welcome to the board.
Does something like this work for you?
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cell In Range("a3:a1000")
  If cell <> "" Then
    With cell.Resize(1, 36)
      .Borders.ColorIndex = 5
    End With
  End If
Next
End Sub
Hope it helps.

[EDIT] Now how can you beat that?
3 different ideas in a 4 minute period.
(Nimrod, Smitty... evenin' fellas.) :biggrin:

[EDIT again...]
I take it back. 4 ideas!
 
Upvote 0
I like Nimrod's Conditional Formatting answer best. (Simple!)

No muss, no fuss, and no mess with the bus!

Smitty

(Heya boys! The season is almost upon us! Where are you Ice Climbing this year N? We've got room in Ouray!)
 
Upvote 0
To Use Conditional Format Method...

1) Select all the cells involved ... eg. A3:AJ1000
2) While the range A3:AJ1000 is selected ...
3) Open up condtional format found on the "format menu"
4) copy this formula into conditional Format window =NOT(ISBLANK($A3))
5) Set your cell format options eg. colored border ...
6) Click OK and your done :wink:

NOTE:
- formula must use a $ before the "A" but NOT before the 3
- the cell reference "$A3" should be the first cell in your selected range
- the "=" is part of the formula that you paste in.
 
Upvote 0
Thanks for that Nimrod. I gave up on conditional formatting long ago when I couldn't figure out how to format based on the contents of another cell. That was long before I discovered VBA and this board and I forgot about it. I like to use code when I can, but that information will be a great help to me in the future. I'm very grateful.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top