D
Dynamo
Hi
This is a continuation from a previous post. Jim Cone kindly informed
me how to change the active range border colour with this piece of
code.
'...
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Dim v As Variant
Dim r As Range
Dim i As Long
= Array(xlEdgeBottom, xlEdgeTop, xlEdgeRight, xlEdgeLeft)
For Each r In Sh.UsedRange.Cells
With r
For i = 0 To 3
..Borders(v(i)).LineStyle = xlNone
Next
End With
Next
For i = 0 To 3
With Target.Borders(v(i))
..LineStyle = xlContinuous
..Weight = xlThick
..ColorIndex = 3
End With
Next
End Sub
'
Problem is that any existing formatting for the workbook, (ie
permanent borders around other cells) is removed. I dont want to use
Pearsons rowliner cos my workbook is for distribution to others so
would prefer to do it using vba. Each sheet in my workbook uses
different ranges (i.e. sheet 1 is A1:O364 sheet 2 is A1:S170 and so
on). So I need a way to retain the existing formatting as well as
change the borders for the active range. Hope that makes sense.
Any help greatly appreciated.
TIA
Dynamo
This is a continuation from a previous post. Jim Cone kindly informed
me how to change the active range border colour with this piece of
code.
'...
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Dim v As Variant
Dim r As Range
Dim i As Long
= Array(xlEdgeBottom, xlEdgeTop, xlEdgeRight, xlEdgeLeft)
For Each r In Sh.UsedRange.Cells
With r
For i = 0 To 3
..Borders(v(i)).LineStyle = xlNone
Next
End With
Next
For i = 0 To 3
With Target.Borders(v(i))
..LineStyle = xlContinuous
..Weight = xlThick
..ColorIndex = 3
End With
Next
End Sub
'
Problem is that any existing formatting for the workbook, (ie
permanent borders around other cells) is removed. I dont want to use
Pearsons rowliner cos my workbook is for distribution to others so
would prefer to do it using vba. Each sheet in my workbook uses
different ranges (i.e. sheet 1 is A1:O364 sheet 2 is A1:S170 and so
on). So I need a way to retain the existing formatting as well as
change the borders for the active range. Hope that makes sense.
Any help greatly appreciated.
TIA
Dynamo