How to change active cell border color in Excel 2007

[Dynamo]

Hi,

I want to change the active cell border colour to red. I foundthe
following code on the net but as a total newbie to VBA in excel I
haven't got a clue where to put it or how to use it. Can somebody
please help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim v As Variant
v = Array(xlEdgeBottom, xlEdgeTop, xlEdgeRight, xlEdgeLeft)
For Each r In ActiveSheet.UsedRange
With r
For i = 0 To 3
..Borders(v(i)).LineStyle = xlNone
Next
End With
Next

For i = 0 To 3
With ActiveCell.Borders(v(i))
..LineStyle = xlContinuous
..Weight = xlThick
..ColorIndex = 7
End With
Next
End Sub

TIA

Dynamo

 

[Jim Cone]

Right-click the sheet tab, choose "View Code" and paste the code into the big white area on the right side.
Then on the "File" menu click "Close and Return to Microsoft Excel"

Suggested changes to code:
1. Just below the line "Dim v As Variant" add these two lines...
Dim r
Dim i

2. Change the line...
.ColorIndex = 7

To:

.ColorIndex = 3
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasXL
(utility download - 3 week trial)
..
..
..

<Dynamo>
wrote in message
Hi,

I want to change the active cell border colour to red. I foundthe
following code on the net but as a total newbie to VBA in excel I
haven't got a clue where to put it or how to use it. Can somebody
please help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim v As Variant
v = Array(xlEdgeBottom, xlEdgeTop, xlEdgeRight, xlEdgeLeft)
For Each r In ActiveSheet.UsedRange
With r
For i = 0 To 3
..Borders(v(i)).LineStyle = xlNone
Next
End With
Next

For i = 0 To 3
With ActiveCell.Borders(v(i))
..LineStyle = xlContinuous
..Weight = xlThick
..ColorIndex = 7
End With
Next
End Sub

TIA

Dynamo

 

[Dynamo]

Ignorance is bliss.

Thanks Jim. I worked out how to do it literally about 5 seconds before
your post. Originally was tring to add it as new macro. Doh.
Anyway, have many sheets within spreadsheet and want do same for all
spreadsheets. Is there a way round to have the activecell changed for
all sheets with just one piece of code. And want happens with merged
cells?

Regards
Paul

 

[Don Guillett Excel MVP]

Ignorance is bliss.

Thanks Jim. I worked out how to do it literally about 5 seconds before
your post.  Originally was tring to add it as new macro. Doh.
Anyway, have many sheets within spreadsheet and want do same for all
spreadsheets. Is there a way round to have the activecell changed for
all sheets with just one piece of code. And want happens with merged
cells?

Regards
Paul






- Show quoted text -

Do you want to change the SAME cell on every sheet
or
when you change sheets and then select another cell change that one
Do you want the changed cells to STAY changed.
When posting it is best to FULLY explain what you want in the FIRST
post.

 

[Gord Dibben]

Remove the code from sheet module.

Open Thisworkbook module.

Use this event type.................

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

your code here

End Sub

As far as merged cells go......have you tested your code on them??

My opinion of merged cells is that they should never have been invented due to
the numerous problems they cause.


Gord Dibben MS Excel MVP

 

[Jim Cone]

Yes, you can accomplish that with one piece of code.
The code needs to be modified, the old code removed and the modified code placed in a different location.
The modified code will work the same on merged cells as it does now.
'---
The modified code is below. It must be placed in the "ThisWorkbook" module.
It is found in the VBA project window (top left) under the name of your workbook.
'---
Jim Cone
http://www.contextures.com/excel-sort-addin.html
(30+ ways to sort)

'---
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim v As Variant
Dim r As Range
Dim i As Long

v = 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(1).Borders(v(i))
..LineStyle = xlContinuous
..Weight = xlThick
..ColorIndex = 3
End With
Next
End Sub
'---

..
..

<Dynamo> wrote in message
Ignorance is bliss.

Thanks Jim. I worked out how to do it literally about 5 seconds before
your post. Originally was tring to add it as new macro. Doh.
Anyway, have many sheets within spreadsheet and want do same for all
spreadsheets. Is there a way round to have the activecell changed for
all sheets with just one piece of code. And want happens with merged
cells?
Regards
Paul

 

[Dynamo]

Jim,
Thank you so much for your patience. Your instructions were so easy to
understand and code works fine. Took me a while to find out where the
vba window was cos didnt have the developers ribbon turned on in excel
options. Anyway, one last question for you now that I can see what the
code actually does. Is it possible to do the same for selected cells
rather than active cells? Problem with existing code is that if more
than one cell is selected only the first cell has the border colour
changed.
Thanks once again.
Paul

 

[Dynamo]

Hi Don,

Appreciate your comment about fully explaining in first post but as a
total newbie, until I could see what the initilal code actually did, I
was unable to assess wether it suited my needs. But I am a fast
learner. Jim Clone has answered my questions but I appreciate your
input.

Paul

 

[Dynamo]

Hi Gord,

Thanks for your input. Much appreciated. Have written another message
in response to Jim Clones input which was much the same as yours and
works fine but now I see what it does it is not exactly what I want.
Needs some fine tuning. If Jim doent come up with an answer then
perhaps you can.

Paul

 

[Jim Cone]

Re: "Is it possible to do the same for selected cells rather than active cells?

Just remove "(1)" in this line...
With Target(1).Borders(v(i))
'---
Also, the entire code set can be simplified by replacing it with...
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Sh.UsedRange.Borders.LineStyle = xlLineStyleNone
Target.Cells.BorderAround xlContinuous, xlThick, 3
End Sub
'---
The above code, however, may not be reliable in xl2010 as in the Tech Preview release of xl2010
the BorderAround method was often ignored
--
Jim Cone

..
..

<Dynamo> wrote in message
Jim,
Thank you so much for your patience. Your instructions were so easy to
understand and code works fine. Took me a while to find out where the
vba window was cos didnt have the developers ribbon turned on in excel
options. Anyway, one last question for you now that I can see what the
code actually does. Is it possible to do the same for selected cells
rather than active cells? Problem with existing code is that if more
than one cell is selected only the first cell has the border colour
changed.
Thanks once again.
Paul

 

[Dynamo]

Jim,
Once again many thanks. Both codes worked fine. Just got my head round
2007 let alone xl2010 so who cares if it aint reliable in xl2010.

Now to get round next problem of changing the cell colour using the
same method. Think I can work that one out for myself now that you've
given me the basic idea.
Cant thank you enough.
Regards
Paul

 

[Dr. Morel]

Do you want to change the SAME cell on every sheet
or
when you change sheets and then select another cell change that one
Do you want the changed cells to STAY changed.
When posting it is best to FULLY explain what you want in the FIRST
post.

You obviously have reading comprehension issues.