Existing sheet formatting removed when changing active cell border color

[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

 

[Don Guillett Excel MVP]

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

I still do not fully understand your need but if??? it is to but a red
border around the cell(s) you select without changing other cells then
just use this in the ThisWorkbook module.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Target.Cells.BorderAround xlContinuous, xlThick, 3
End Sub
However, this is probably not what you want. Explain with examples and/
or accept my original offer.

 

[Dynamo]

OK. To fully explain. When a cell or range of cells is selected, excel
automatically places a thick black border around that cell or range of
cells. However, it is not always easy to see which cells are selected
especially if the existing cell border is black as well. I cant see
anywhere in the excel properties page where you can change the style
of the active cell selection. I have hyperlinks on the first sheet of
my workbook that bookmark certain cells on other sheets. When you
click on the hyperlink and it goes to the cells bookmarked sheet I
want that cell or range of cells to stand out from the others either
by changing the fill color or the border color. Any existing
formatting to the workbook should not be affected and also the active
cells should revert back to their original formatting when they are no
longer the active cell. Jim Cones original code almost did the job
until I noticed that other sheet formats such as ALL existing borders
were lost.

I haven't tried your code yet so will get back to you on that one but
hope the above better explains my needs.

TIA
Dynamo

 

[Dynamo]

Further to my previous message I think I have now managed it. The code
below changes the fill colour of the active cell or range of selected
cells without changing any existing formatting. Hoorah!! OK so it
doesnt change the borders which was my original question but it is a
major step forward.
'...
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Cells.FormatConditions.Delete
Target.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
Target.FormatConditions(1).Interior.ColorIndex = 27
End Sub
'