Help With Offset Method

[Macropheliac]

Hello,
Can anyone tell me the proper syntax to shorten the code below?
In other words:

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

Any help is greatly appreciated.

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

 

[Smitty]

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:

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

You almost had it.

 

[Nimrod]

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.

 

[HalfAce]

Hello Macropheliac, welcome to the board.
Does something like this work for you?

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

[EDIT again...]
I take it back. 4 ideas!

 

[Macropheliac]

3 MVPs, 3 Perfections

Thank all of you for the help. You guys are amazing.

 

[Smitty]

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

 

[Nimrod]

Hello Pennysaver:

Haven't got any Ice Climbing trips planned yet but the hills open for Snowboarding on Nov 24 ... maybe you should come up for a couple of runs down the hil !!

http://www.skisilverstar.com/

 

[Macropheliac]

Hello Nimrod,
How exactly would I conditional format cells based on another cell's contents?

 

[Nimrod]

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.

 

[Macropheliac]

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.