Offset Syntax

Danny Ray

Board Regular
Joined
May 28, 2005
Messages
79
Hello,
Can someone tell me the proper suntax to condense the following
bit of code?

Thanks.


Code:
With Target
 .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, -1).Borders.ColorIndex = 5
 .Offset(0, -2).Borders.ColorIndex = 5
 .Offset(0, -3).Borders.ColorIndex = 5
 .Borders.ColorIndex = 5
 End With
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
With Target.Offset(0, -3).Resize(Target.Rows.Count, 38)
   .Borders.ColorIndex = 5 
End With
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
How about:
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> a()
X = "D4"
Range(Range(X).Offset(0, -3).Address & ":" & Range(X).Offset(0, 35).Address).Borders.ColorIndex = 5
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
NP but I like the resize method too, it's a little less typing :LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,119,137
Messages
5,576,300
Members
412,716
Latest member
thviid
Top