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

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
news:(E-Mail Removed)...
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

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
On Thu, 25 Nov 2010 09:48:50 -0800, "Jim Cone"
<(E-Mail Removed)> wrote:

>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

On Nov 25, 11:56*am, Dynamo wrote:
> 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
> On Thu, 25 Nov 2010 09:48:50 -0800, "Jim Cone"
>
>
>
> <james.cone...@comcast.netXXX> wrote:
> >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- Hide quoted text -
>
> - 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.

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

On Thu, 25 Nov 2010 17:56:43 +0000, Dynamo wrote:

>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
>On Thu, 25 Nov 2010 09:48:50 -0800, "Jim Cone"
><(E-Mail Removed)> wrote:
>
>>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

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
news:(E-Mail Removed)...
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

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

On Thu, 25 Nov 2010 10:44:23 -0800, "Jim Cone"
<(E-Mail Removed)> wrote:

>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
>news:(E-Mail Removed)...
>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

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

On Thu, 25 Nov 2010 10:36:27 -0800 (PST), Don Guillett Excel MVP
<(E-Mail Removed)> wrote:

>On Nov 25, 11:56*am, Dynamo wrote:
>> 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
>> On Thu, 25 Nov 2010 09:48:50 -0800, "Jim Cone"
>>
>>
>>
>> <james.cone...@comcast.netXXX> wrote:
>> >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- Hide quoted text -
>>
>> - 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.

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

On Thu, 25 Nov 2010 10:37:20 -0800, Gord Dibben <(E-Mail Removed)>
wrote:

>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
>
>On Thu, 25 Nov 2010 17:56:43 +0000, Dynamo wrote:
>
>>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
>>On Thu, 25 Nov 2010 09:48:50 -0800, "Jim Cone"
>><(E-Mail Removed)> wrote:
>>
>>>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

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
news:(E-Mail Removed)...
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