Modify code to insert borders
 

I need the following code (currently operating correctly) changed so that
after the new worksheet (Sheet3, or Sheet4, or Sheet5) is opened, and cell
€œA1€� selected:

For line v(1,1)€¦
1. Range B2:D2 (composed of three merged cell ranges, B2:C2, B3:C4, D2:T4)
has a border put around it (a highlight to draw the users attention):
a. With Selection.Borders(xlEdgeLeft), (xlEdgeRight), (xlEdgeTop),
(xlEdgeBottom)
b. .LineStyle = xlContinuous
c. .Weight = xlThick
d. .ColorIndex = 41

2. When the user leaves the worksheet, or makes any click, or the workbook
is closed, the border is returned to
a. With Selection.Borders(xlEdgeLeft), (xlEdgeRight), (xlEdgeTop),
(xlEdgeBottom)
b. .LineStyle = xlContinuous
c. .Weight = xlThin
d. .ColorIndex = xlAutomatic

For line v(2,1)€¦
1. Range B12:D12 (composed of three merged cell ranges, B12:C12, B13:C14,
D12:T14) has a border put around it (a highlight to draw the users
attention):
a. Same as for v(1,1)€¦
b.
c.
d.
2. a. Same as for v(1,1)€¦
b.
c.
d.

For v(3,1)€¦
The same for each line €“ the only thing changing is the range name.
v(16,1)€¦


Option Explicit

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim v(1 To 16, 1 To 3) As String
Dim rng1 As Range
Dim i As Long

v(1, 1) = "C9": v(1, 2) = "Sheet3": v(1, 3) = "A1" €˜User clicks cell C9, is
moved to Sheet3, Cell A1
v(2, 1) = "C15": v(2, 2) = "Sheet3": v(2, 3) = "A1"
v(3, 1) = "C19": v(3, 2) = "Sheet3": v(3, 3) = "A1"
v(4, 1) = "C23": v(4, 2) = "Sheet3": v(4, 3) = "A1"
v(5, 1) = "C27": v(5, 2) = "Sheet3": v(5, 3) = "A1"
v(6, 1) = "C36": v(6, 2) = "Sheet3": v(6, 3) = "A1"
v(7, 1) = "H9": v(7, 2) = "Sheet4": v(7, 3) = "A1"
v(8, 1) = "H13": v(8, 2) = "Sheet4": v(8, 3) = "A1"
v(9, 1) = "H16": v(9, 2) = "Sheet4": v(9, 3) = "A1"
v(10, 1) = "H19": v(10, 2) = "Sheet4": v(10, 3) = "A1"
v(11, 1) = "H23": v(11, 2) = "Sheet4": v(11, 3) = "A1"
v(12, 1) = "H30": v(12, 2) = "Sheet4": v(12, 3) = "A1"
v(13, 1) = "M9": v(13, 2) = "Sheet5": v(13, 3) = "A1"
v(14, 1) = "M12": v(14, 2) = "Sheet5": v(14, 3) = "A1"
v(15, 1) = "M21": v(15, 2) = "Sheet5": v(15, 3) = "A1"
v(16, 1) = "M25": v(16, 2) = "Sheet5": v(16, 3) = "A1"

For i = 1 To 16
If Target.Address = Range(v(i, 1)).MergeArea.Address Then

Application.ScreenUpdating = False
Set rng1 = Sheets(v(i, 2)).Range(v(i, 3))
Sheets(v(i, 2)).Select
ActiveWindow.Zoom = 80
ActiveWindow.ScrollRow = rng1.Row
ActiveWindow.ScrollColumn = rng1.Column
Application.ScreenUpdating = True

Exit For
End If

Next
End Sub

Modify code to insert borders
 

Record a macro to do it, then add the code to yours.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
I need the following code (currently operating correctly) changed so that
after the new worksheet (Sheet3, or Sheet4, or Sheet5) is opened, and cell
"A1" selected:

For line v(1,1).
1. Range B2:D2 (composed of three merged cell ranges, B2:C2, B3:C4, D2:T4)
has a border put around it (a highlight to draw the user's attention):
a. With Selection.Borders(xlEdgeLeft), (xlEdgeRight), (xlEdgeTop),
(xlEdgeBottom)
b. .LineStyle = xlContinuous
c. .Weight = xlThick
d. .ColorIndex = 41

2. When the user leaves the worksheet, or makes any click, or the workbook
is closed, the border is returned to
a. With Selection.Borders(xlEdgeLeft), (xlEdgeRight), (xlEdgeTop),
(xlEdgeBottom)
b. .LineStyle = xlContinuous
c. .Weight = xlThin
d. .ColorIndex = xlAutomatic

For line v(2,1).
1. Range B12:D12 (composed of three merged cell ranges, B12:C12, B13:C14,
D12:T14) has a border put around it (a highlight to draw the user's
attention):
a. Same as for v(1,1).
b.
c.
d.
2. a. Same as for v(1,1).
b.
c.
d.

For v(3,1).
The same for each line - the only thing changing is the range name.
v(16,1).


Option Explicit

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim v(1 To 16, 1 To 3) As String
Dim rng1 As Range
Dim i As Long

v(1, 1) = "C9": v(1, 2) = "Sheet3": v(1, 3) = "A1" 'User clicks cell C9,
is
moved to Sheet3, Cell A1
v(2, 1) = "C15": v(2, 2) = "Sheet3": v(2, 3) = "A1"
v(3, 1) = "C19": v(3, 2) = "Sheet3": v(3, 3) = "A1"
v(4, 1) = "C23": v(4, 2) = "Sheet3": v(4, 3) = "A1"
v(5, 1) = "C27": v(5, 2) = "Sheet3": v(5, 3) = "A1"
v(6, 1) = "C36": v(6, 2) = "Sheet3": v(6, 3) = "A1"
v(7, 1) = "H9": v(7, 2) = "Sheet4": v(7, 3) = "A1"
v(8, 1) = "H13": v(8, 2) = "Sheet4": v(8, 3) = "A1"
v(9, 1) = "H16": v(9, 2) = "Sheet4": v(9, 3) = "A1"
v(10, 1) = "H19": v(10, 2) = "Sheet4": v(10, 3) = "A1"
v(11, 1) = "H23": v(11, 2) = "Sheet4": v(11, 3) = "A1"
v(12, 1) = "H30": v(12, 2) = "Sheet4": v(12, 3) = "A1"
v(13, 1) = "M9": v(13, 2) = "Sheet5": v(13, 3) = "A1"
v(14, 1) = "M12": v(14, 2) = "Sheet5": v(14, 3) = "A1"
v(15, 1) = "M21": v(15, 2) = "Sheet5": v(15, 3) = "A1"
v(16, 1) = "M25": v(16, 2) = "Sheet5": v(16, 3) = "A1"

For i = 1 To 16
If Target.Address = Range(v(i, 1)).MergeArea.Address Then

Application.ScreenUpdating = False
Set rng1 = Sheets(v(i, 2)).Range(v(i, 3))
Sheets(v(i, 2)).Select
ActiveWindow.Zoom = 80
ActiveWindow.ScrollRow = rng1.Row
ActiveWindow.ScrollColumn = rng1.Column
Application.ScreenUpdating = True

Exit For
End If

Next
End Sub

Modify code to insert borders
 

I tried but couldn't get anything to work. I think it is a matter of just
where to insert the code

"Bob Phillips" wrote:

Record a macro to do it, then add the code to yours.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
I need the following code (currently operating correctly) changed so that
after the new worksheet (Sheet3, or Sheet4, or Sheet5) is opened, and cell
"A1" selected:

For line v(1,1).
1. Range B2:D2 (composed of three merged cell ranges, B2:C2, B3:C4, D2:T4)
has a border put around it (a highlight to draw the user's attention):
a. With Selection.Borders(xlEdgeLeft), (xlEdgeRight), (xlEdgeTop),
(xlEdgeBottom)
b. .LineStyle = xlContinuous
c. .Weight = xlThick
d. .ColorIndex = 41

2. When the user leaves the worksheet, or makes any click, or the workbook
is closed, the border is returned to
a. With Selection.Borders(xlEdgeLeft), (xlEdgeRight), (xlEdgeTop),
(xlEdgeBottom)
b. .LineStyle = xlContinuous
c. .Weight = xlThin
d. .ColorIndex = xlAutomatic

For line v(2,1).
1. Range B12:D12 (composed of three merged cell ranges, B12:C12, B13:C14,
D12:T14) has a border put around it (a highlight to draw the user's
attention):
a. Same as for v(1,1).
b.
c.
d.
2. a. Same as for v(1,1).
b.
c.
d.

For v(3,1).
The same for each line - the only thing changing is the range name.
v(16,1).


Option Explicit

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim v(1 To 16, 1 To 3) As String
Dim rng1 As Range
Dim i As Long

v(1, 1) = "C9": v(1, 2) = "Sheet3": v(1, 3) = "A1" 'User clicks cell C9,
is
moved to Sheet3, Cell A1
v(2, 1) = "C15": v(2, 2) = "Sheet3": v(2, 3) = "A1"
v(3, 1) = "C19": v(3, 2) = "Sheet3": v(3, 3) = "A1"
v(4, 1) = "C23": v(4, 2) = "Sheet3": v(4, 3) = "A1"
v(5, 1) = "C27": v(5, 2) = "Sheet3": v(5, 3) = "A1"
v(6, 1) = "C36": v(6, 2) = "Sheet3": v(6, 3) = "A1"
v(7, 1) = "H9": v(7, 2) = "Sheet4": v(7, 3) = "A1"
v(8, 1) = "H13": v(8, 2) = "Sheet4": v(8, 3) = "A1"
v(9, 1) = "H16": v(9, 2) = "Sheet4": v(9, 3) = "A1"
v(10, 1) = "H19": v(10, 2) = "Sheet4": v(10, 3) = "A1"
v(11, 1) = "H23": v(11, 2) = "Sheet4": v(11, 3) = "A1"
v(12, 1) = "H30": v(12, 2) = "Sheet4": v(12, 3) = "A1"
v(13, 1) = "M9": v(13, 2) = "Sheet5": v(13, 3) = "A1"
v(14, 1) = "M12": v(14, 2) = "Sheet5": v(14, 3) = "A1"
v(15, 1) = "M21": v(15, 2) = "Sheet5": v(15, 3) = "A1"
v(16, 1) = "M25": v(16, 2) = "Sheet5": v(16, 3) = "A1"

For i = 1 To 16
If Target.Address = Range(v(i, 1)).MergeArea.Address Then

Application.ScreenUpdating = False
Set rng1 = Sheets(v(i, 2)).Range(v(i, 3))
Sheets(v(i, 2)).Select
ActiveWindow.Zoom = 80
ActiveWindow.ScrollRow = rng1.Row
ActiveWindow.ScrollColumn = rng1.Column
Application.ScreenUpdating = True

Exit For
End If

Next
End Sub