Highlighting

gwenturpin · #1 06-13-2005, 06:54 AM

I am wanting to change the default highlighting when you hover over a cell/row.

E.g. I will be working on a large list of contact details when for instance I click on a surname I would like the whole row to jump out e.g be in a white box with bold letters coloured red.

Any ideas????

mangesh_yadav · #2 06-13-2005, 07:05 AM

Enter the following code in the module for the sheet in question.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells.Interior.ColorIndex = xlNone
Cells.Font.Bold = False
Cells.Font.ColorIndex = 1

Target.EntireRow.Interior.ColorIndex = 3
Target.EntireRow.Font.Bold = True
Target.EntireRow.Font.ColorIndex = 2

End Sub

To enter the code, right-click on the sheet name tab below, select view code, and enetr the code.

Mangesh

gwenturpin · #3 06-13-2005, 07:12 AM

I have completed that and when I hover it comes up with the following message

Compile error:
expected end sub

gwenturpin · #4 06-13-2005, 07:19 AM

Thanks you its worked!!!

Can I make the text bigger or have the column headings a different colour

mangesh_yadav · #5 06-13-2005, 07:29 AM

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells.Interior.ColorIndex = xlNone
Cells.Font.Bold = False
Cells.Font.ColorIndex = 1
Cells.Font.Size = 10 ' your normal size here

' teatment for row 1
Range("1:1").Interior.ColorIndex = 5

If Target.Row > 1 Then
Target.EntireRow.Interior.ColorIndex = 3
Target.EntireRow.Font.Bold = True
Target.EntireRow.Font.ColorIndex = 2
Target.EntireRow.Font.Size = 14
End If
End Sub

font.size changes the size.
for the header row which is one in this case, you can add more lines as you wish.

Mangesh

#6 06-13-2005, 08:05 AM

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = xlColorIndexNone
.Font.ColorIndex = 3
.Font.Bold = True
End With
End With

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

"gwenturpin" <gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com> wrote
in message news:gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com...
>
>

>
> I am wanting to change the default highlighting when you hover over a
> cell/row.
>
> E.g. I will be working on a large list of contact details when for
> instance I click on a surname I would like the whole row to jump out
> e.g be in a white box with bold letters coloured red.
>
> Any ideas????
>
>
> --
> gwenturpin
> ------------------------------------------------------------------------
> gwenturpin's Profile:
http://www.excelforum.com/member.php...o&userid=24239
> View this thread: http://www.excelforum.com/showthread...hreadid=378497
>

#7 06-13-2005, 08:05 AM

Gwenturpin

See this Add-in
http://www.cpearson.com/excel/RowLiner.htm

Regards

Pank

"Bob Phillips" wrote:

> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Cells.FormatConditions.Delete
> With Target.EntireRow
> .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> With .FormatConditions(1)
> With .Borders(xlTop)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = 5
> End With
> With .Borders(xlBottom)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = 5
> End With
> .Interior.ColorIndex = xlColorIndexNone
> .Font.ColorIndex = 3
> .Font.Bold = True
> End With
> End With
>
>
> End Sub
>
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
>
>
> --
> HTH
>
> Bob Phillips
>
> "gwenturpin" <gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com> wrote
> in message news:gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com...
> >
> >

> >
> > I am wanting to change the default highlighting when you hover over a
> > cell/row.
> >
> > E.g. I will be working on a large list of contact details when for
> > instance I click on a surname I would like the whole row to jump out
> > e.g be in a white box with bold letters coloured red.
> >
> > Any ideas????
> >
> >
> > --
> > gwenturpin
> > ------------------------------------------------------------------------
> > gwenturpin's Profile:
> http://www.excelforum.com/member.php...o&userid=24239
> > View this thread: http://www.excelforum.com/showthread...hreadid=378497
> >
>
>
>

#8 06-13-2005, 09:05 AM

Cool Bob,,
I'd like to have this macro available (ON CALL) to apply to numerous
workbooks/worksheets I work with. How would I go about doing this?
Could I put a new button on my toolbar to run the macro;
and have some code in my personal.xls; maybe some type
of toggle (On/Off) feature...?? of course applying to the Activesheet..
TIA,
Jim

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:uv91HtAcFHA.3808@TK2MSFTNGP14.phx.gbl...
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Cells.FormatConditions.Delete
> With Target.EntireRow
> .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> With .FormatConditions(1)
> With .Borders(xlTop)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = 5
> End With
> With .Borders(xlBottom)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = 5
> End With
> .Interior.ColorIndex = xlColorIndexNone
> .Font.ColorIndex = 3
> .Font.Bold = True
> End With
> End With
>
>
> End Sub
>
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
>
>
> --
> HTH
>
> Bob Phillips
>
> "gwenturpin" <gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com>
wrote
> in message news:gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com...
> >
> >

> >
> > I am wanting to change the default highlighting when you hover over a
> > cell/row.
> >
> > E.g. I will be working on a large list of contact details when for
> > instance I click on a surname I would like the whole row to jump out
> > e.g be in a white box with bold letters coloured red.
> >
> > Any ideas????
> >
> >
> > --
> > gwenturpin
> > ------------------------------------------------------------------------
> > gwenturpin's Profile:
> http://www.excelforum.com/member.php...o&userid=24239
> > View this thread:
http://www.excelforum.com/showthread...hreadid=378497
> >
>
>

#9 06-13-2005, 09:05 AM

Jim,

Do you mean that you would like to have it apply to the activesheet on
demand? Problem here is that it is event code so we need to setup that
event, remove it on say shift toggle, and what about if we already have that
event.

What I would do is add application events, and setup a sheet dependent name
that is set/unset by the button.

Sound good?

--
HTH

Bob Phillips

"Jim May" <jmay@cox.net> wrote in message
news:qGere.49221$Fv.38665@lakeread01...
> Cool Bob,,
> I'd like to have this macro available (ON CALL) to apply to numerous
> workbooks/worksheets I work with. How would I go about doing this?
> Could I put a new button on my toolbar to run the macro;
> and have some code in my personal.xls; maybe some type
> of toggle (On/Off) feature...?? of course applying to the Activesheet..
> TIA,
> Jim
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:uv91HtAcFHA.3808@TK2MSFTNGP14.phx.gbl...
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > Cells.FormatConditions.Delete
> > With Target.EntireRow
> > .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> > With .FormatConditions(1)
> > With .Borders(xlTop)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = 5
> > End With
> > With .Borders(xlBottom)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = 5
> > End With
> > .Interior.ColorIndex = xlColorIndexNone
> > .Font.ColorIndex = 3
> > .Font.Bold = True
> > End With
> > End With
> >
> >
> > End Sub
> >
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "gwenturpin" <gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com>
> wrote
> > in message
news:gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com...
> > >
> > >

> > >
> > > I am wanting to change the default highlighting when you hover over a
> > > cell/row.
> > >
> > > E.g. I will be working on a large list of contact details when for
> > > instance I click on a surname I would like the whole row to jump out
> > > e.g be in a white box with bold letters coloured red.
> > >
> > > Any ideas????
> > >
> > >
> > > --
> > > gwenturpin
> >
> ------------------------------------------------------------------------
> > > gwenturpin's Profile:
> > http://www.excelforum.com/member.php...o&userid=24239
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=378497
> > >
> >
> >
>
>

#10 06-13-2005, 06:05 PM

Do you mean that you would like to have it apply to the activesheet on
demand? YES
and what about if we already have that
event. SHOULD NOT EVER
What I would do is add application events, and setup a sheet dependent name
that is set/unset by the button. HOW IS THIS DONE?
Jim

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:uzoSueBcFHA.228@TK2MSFTNGP12.phx.gbl...
> Jim,
>
> Do you mean that you would like to have it apply to the activesheet on
> demand? Problem here is that it is event code so we need to setup that
> event, remove it on say shift toggle, and what about if we already have
that
> event.
>
> What I would do is add application events, and setup a sheet dependent
name
> that is set/unset by the button.
>
> Sound good?
>
> --
> HTH
>
> Bob Phillips
>
> "Jim May" <jmay@cox.net> wrote in message
> news:qGere.49221$Fv.38665@lakeread01...
> > Cool Bob,,
> > I'd like to have this macro available (ON CALL) to apply to numerous
> > workbooks/worksheets I work with. How would I go about doing this?
> > Could I put a new button on my toolbar to run the macro;
> > and have some code in my personal.xls; maybe some type
> > of toggle (On/Off) feature...?? of course applying to the Activesheet..
> > TIA,
> > Jim
> >
> > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > news:uv91HtAcFHA.3808@TK2MSFTNGP14.phx.gbl...
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > Cells.FormatConditions.Delete
> > > With Target.EntireRow
> > > .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> > > With .FormatConditions(1)
> > > With .Borders(xlTop)
> > > .LineStyle = xlContinuous
> > > .Weight = xlThin
> > > .ColorIndex = 5
> > > End With
> > > With .Borders(xlBottom)
> > > .LineStyle = xlContinuous
> > > .Weight = xlThin
> > > .ColorIndex = 5
> > > End With
> > > .Interior.ColorIndex = xlColorIndexNone
> > > .Font.ColorIndex = 3
> > > .Font.Bold = True
> > > End With
> > > End With
> > >
> > >
> > > End Sub
> > >
> > >
> > > 'This is worksheet event code, which means that it needs to be
> > > 'placed in the appropriate worksheet code module, not a standard
> > > 'code module. To do this, right-click on the sheet tab, select
> > > 'the View Code option from the menu, and paste the code in.
> > >
> > >
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "gwenturpin" <gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com>
> > wrote
> > > in message
> news:gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com...
> > > >
> > > >

> > > >
> > > > I am wanting to change the default highlighting when you hover over
a
> > > > cell/row.
> > > >
> > > > E.g. I will be working on a large list of contact details when for
> > > > instance I click on a surname I would like the whole row to jump out
> > > > e.g be in a white box with bold letters coloured red.
> > > >
> > > > Any ideas????
> > > >
> > > >
> > > > --
> > > > gwenturpin
> > >
> > ------------------------------------------------------------------------
> > > > gwenturpin's Profile:
> > > http://www.excelforum.com/member.php...o&userid=24239
> > > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=378497
> > > >
> > >
> > >
> >
> >
>
>

#11 06-13-2005, 10:05 PM

Okay Jim,

There are 3 parts to this.

The first part is just a simple piece of code that creates a toolbar button
(I am appending it to the format toolbar).

The second part is to setup application events, and trap the application
sheet selectionchange event and do our highlighting.

These first two parts are both included in the ThisWorkbook module, and
should be included in Personal.xls.

The 3rd part is the macro that is run when the button is clicked, and either
turns highlighting on the active sheet on or off (by use of a worksheet
scope name). SO cliciking once sets highlighting on that page, a second time
removes it (it could be shift-click to remove if preferred).This goes in a
standard code module, also in Personal.xls.

BTW, in my version that I installed with some colleagues, we had row and
column highlighting, but only for 5 cells either side. Would you like this
version?

>>>>>>>>>>> ThisWorkbook

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
Dim hilite As Boolean
hilite = False
On Error Resume Next
hilite = Evaluate(Sh.Parent.Names(Sh.Name & "!__Hilite").RefersTo)
On Error GoTo 0
If hilite Then
Sh.Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = xlColorIndexNone
.Font.ColorIndex = 3
.Font.Bold = True
End With
End With
End If
End Sub

Private Sub Workbook_Open()
Dim oCtl As CommandBarControl
Set App = Application

On Error Resume Next
Application.CommandBars("Formatting").Controls("Hilite").Delete
On Error GoTo 0

With Application.CommandBars("Formatting")
Set oCtl = .Controls.Add(Type:=msoControlButton, Temporary:=True)
oCtl.Caption = "Hilite"
oCtl.Style = msoButtonIconAndCaption
oCtl.FaceId = 340
oCtl.OnAction = "SetupHilite"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Formatting").Controls("Hilite").Delete
On Error GoTo 0
End Sub

>>>>>>>>> Standard code module

Public Sub SetupHilite()
Dim hilite As Boolean
With ActiveWorkbook
hilite = False
On Error Resume Next
hilite = Evaluate(.Names(ActiveSheet.Name &
"!__Hilite").RefersTo)
On Error GoTo 0
ActiveSheet.Cells.FormatConditions.Delete
.Names.Add Name:=ActiveSheet.Name & "!__Hilite", RefersTo:=Not
hilite
.Names(ActiveSheet.Name & "!__Hilite").Visible = False
End With
End Sub

--
HTH

Bob Phillips

"Jim May" <jmay@cox.net> wrote in message
news:8nnre.49257$Fv.13329@lakeread01...
> Do you mean that you would like to have it apply to the activesheet on
> demand? YES
> and what about if we already have that
> event. SHOULD NOT EVER
> What I would do is add application events, and setup a sheet dependent
name
> that is set/unset by the button. HOW IS THIS DONE?
> Jim
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:uzoSueBcFHA.228@TK2MSFTNGP12.phx.gbl...
> > Jim,
> >
> > Do you mean that you would like to have it apply to the activesheet on
> > demand? Problem here is that it is event code so we need to setup that
> > event, remove it on say shift toggle, and what about if we already have
> that
> > event.
> >
> > What I would do is add application events, and setup a sheet dependent
> name
> > that is set/unset by the button.
> >
> > Sound good?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Jim May" <jmay@cox.net> wrote in message
> > news:qGere.49221$Fv.38665@lakeread01...
> > > Cool Bob,,
> > > I'd like to have this macro available (ON CALL) to apply to numerous
> > > workbooks/worksheets I work with. How would I go about doing this?
> > > Could I put a new button on my toolbar to run the macro;
> > > and have some code in my personal.xls; maybe some type
> > > of toggle (On/Off) feature...?? of course applying to the
Activesheet..
> > > TIA,
> > > Jim
> > >
> > > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > > news:uv91HtAcFHA.3808@TK2MSFTNGP14.phx.gbl...
> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > Cells.FormatConditions.Delete
> > > > With Target.EntireRow
> > > > .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> > > > With .FormatConditions(1)
> > > > With .Borders(xlTop)
> > > > .LineStyle = xlContinuous
> > > > .Weight = xlThin
> > > > .ColorIndex = 5
> > > > End With
> > > > With .Borders(xlBottom)
> > > > .LineStyle = xlContinuous
> > > > .Weight = xlThin
> > > > .ColorIndex = 5
> > > > End With
> > > > .Interior.ColorIndex = xlColorIndexNone
> > > > .Font.ColorIndex = 3
> > > > .Font.Bold = True
> > > > End With
> > > > End With
> > > >
> > > >
> > > > End Sub
> > > >
> > > >
> > > > 'This is worksheet event code, which means that it needs to be
> > > > 'placed in the appropriate worksheet code module, not a standard
> > > > 'code module. To do this, right-click on the sheet tab, select
> > > > 'the View Code option from the menu, and paste the code in.
> > > >
> > > >
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "gwenturpin"
<gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com>
> > > wrote
> > > > in message
> > news:gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com...
> > > > >
> > > > >

> > > > >
> > > > > I am wanting to change the default highlighting when you hover
over
> a
> > > > > cell/row.
> > > > >
> > > > > E.g. I will be working on a large list of contact details when for
> > > > > instance I click on a surname I would like the whole row to jump
out
> > > > > e.g be in a white box with bold letters coloured red.
> > > > >
> > > > > Any ideas????
> > > > >
> > > > >
> > > > > --
> > > > > gwenturpin
> > > >
> >
> ------------------------------------------------------------------------
> > > > > gwenturpin's Profile:
> > > > http://www.excelforum.com/member.php...o&userid=24239
> > > > > View this thread:
> > > http://www.excelforum.com/showthread...hreadid=378497
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

#12 06-13-2005, 10:05 PM

Thanks for the code:
When I click on the Icon (after loading everything) the code "bombs" and
highlights the word hilite, within ...:=Not hilite.Names(... below..

"!__Hilite", RefersTo:=Not hilite.Names(ActiveSheet.Name &
"!__Hilite").Visible = False
End With
End Sub

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:uKJEAFIcFHA.3808@TK2MSFTNGP14.phx.gbl...
> Okay Jim,
>
> There are 3 parts to this.
>
> The first part is just a simple piece of code that creates a toolbar
button
> (I am appending it to the format toolbar).
>
> The second part is to setup application events, and trap the application
> sheet selectionchange event and do our highlighting.
>
> These first two parts are both included in the ThisWorkbook module, and
> should be included in Personal.xls.
>
> The 3rd part is the macro that is run when the button is clicked, and
either
> turns highlighting on the active sheet on or off (by use of a worksheet
> scope name). SO cliciking once sets highlighting on that page, a second
time
> removes it (it could be shift-click to remove if preferred).This goes in a
> standard code module, also in Personal.xls.
>
> BTW, in my version that I installed with some colleagues, we had row and
> column highlighting, but only for 5 cells either side. Would you like this
> version?
>
> >>>>>>>>>>> ThisWorkbook
>
> Option Explicit
>
> Public WithEvents App As Application
>
> Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
> Range)
> Dim hilite As Boolean
> hilite = False
> On Error Resume Next
> hilite = Evaluate(Sh.Parent.Names(Sh.Name & "!__Hilite").RefersTo)
> On Error GoTo 0
> If hilite Then
> Sh.Cells.FormatConditions.Delete
> With Target.EntireRow
> .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> With .FormatConditions(1)
> With .Borders(xlTop)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = 5
> End With
> With .Borders(xlBottom)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = 5
> End With
> .Interior.ColorIndex = xlColorIndexNone
> .Font.ColorIndex = 3
> .Font.Bold = True
> End With
> End With
> End If
> End Sub
>
> Private Sub Workbook_Open()
> Dim oCtl As CommandBarControl
> Set App = Application
>
> On Error Resume Next
> Application.CommandBars("Formatting").Controls("Hilite").Delete
> On Error GoTo 0
>
> With Application.CommandBars("Formatting")
> Set oCtl = .Controls.Add(Type:=msoControlButton, Temporary:=True)
> oCtl.Caption = "Hilite"
> oCtl.Style = msoButtonIconAndCaption
> oCtl.FaceId = 340
> oCtl.OnAction = "SetupHilite"
> End With
> End Sub
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> On Error Resume Next
> Application.CommandBars("Formatting").Controls("Hilite").Delete
> On Error GoTo 0
> End Sub
>
>
> >>>>>>>>> Standard code module
>
>
> Public Sub SetupHilite()
> Dim hilite As Boolean
> With ActiveWorkbook
> hilite = False
> On Error Resume Next
> hilite = Evaluate(.Names(ActiveSheet.Name &
> "!__Hilite").RefersTo)
> On Error GoTo 0
> ActiveSheet.Cells.FormatConditions.Delete
> .Names.Add Name:=ActiveSheet.Name & "!__Hilite", RefersTo:=Not
> hilite
> .Names(ActiveSheet.Name & "!__Hilite").Visible = False
> End With
> End Sub
>
> --
> HTH
>
> Bob Phillips
>
> "Jim May" <jmay@cox.net> wrote in message
> news:8nnre.49257$Fv.13329@lakeread01...
> > Do you mean that you would like to have it apply to the activesheet on
> > demand? YES
> > and what about if we already have that
> > event. SHOULD NOT EVER
> > What I would do is add application events, and setup a sheet dependent
> name
> > that is set/unset by the button. HOW IS THIS DONE?
> > Jim
> >
> > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > news:uzoSueBcFHA.228@TK2MSFTNGP12.phx.gbl...
> > > Jim,
> > >
> > > Do you mean that you would like to have it apply to the activesheet on
> > > demand? Problem here is that it is event code so we need to setup that
> > > event, remove it on say shift toggle, and what about if we already
have
> > that
> > > event.
> > >
> > > What I would do is add application events, and setup a sheet dependent
> > name
> > > that is set/unset by the button.
> > >
> > > Sound good?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Jim May" <jmay@cox.net> wrote in message
> > > news:qGere.49221$Fv.38665@lakeread01...
> > > > Cool Bob,,
> > > > I'd like to have this macro available (ON CALL) to apply to numerous
> > > > workbooks/worksheets I work with. How would I go about doing this?
> > > > Could I put a new button on my toolbar to run the macro;
> > > > and have some code in my personal.xls; maybe some type
> > > > of toggle (On/Off) feature...?? of course applying to the
> Activesheet..
> > > > TIA,
> > > > Jim
> > > >
> > > > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > > > news:uv91HtAcFHA.3808@TK2MSFTNGP14.phx.gbl...
> > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > Cells.FormatConditions.Delete
> > > > > With Target.EntireRow
> > > > > .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> > > > > With .FormatConditions(1)
> > > > > With .Borders(xlTop)
> > > > > .LineStyle = xlContinuous
> > > > > .Weight = xlThin
> > > > > .ColorIndex = 5
> > > > > End With
> > > > > With .Borders(xlBottom)
> > > > > .LineStyle = xlContinuous
> > > > > .Weight = xlThin
> > > > > .ColorIndex = 5
> > > > > End With
> > > > > .Interior.ColorIndex = xlColorIndexNone
> > > > > .Font.ColorIndex = 3
> > > > > .Font.Bold = True
> > > > > End With
> > > > > End With
> > > > >
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > > 'This is worksheet event code, which means that it needs to be
> > > > > 'placed in the appropriate worksheet code module, not a standard
> > > > > 'code module. To do this, right-click on the sheet tab, select
> > > > > 'the View Code option from the menu, and paste the code in.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "gwenturpin"
> <gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com>
> > > > wrote
> > > > > in message
> > > news:gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com...
> > > > > >
> > > > > >

> > > > > >
> > > > > > I am wanting to change the default highlighting when you hover
> over
> > a
> > > > > > cell/row.
> > > > > >
> > > > > > E.g. I will be working on a large list of contact details when
for
> > > > > > instance I click on a surname I would like the whole row to jump
> out
> > > > > > e.g be in a white box with bold letters coloured red.
> > > > > >
> > > > > > Any ideas????
> > > > > >
> > > > > >
> > > > > > --
> > > > > > gwenturpin
> > > > >
> > >
> > ------------------------------------------------------------------------
> > > > > > gwenturpin's Profile:
> > > > > http://www.excelforum.com/member.php...o&userid=24239
> > > > > > View this thread:
> > > > http://www.excelforum.com/showthread...hreadid=378497
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

#13 06-14-2005, 05:05 AM

Wrap-around again Jim. use this

>>>>>>>>> Standard code module

Public Sub SetupHilite()
Dim hilite As Boolean
With ActiveWorkbook
hilite = False
On Error Resume Next
hilite = Evaluate(.Names(ActiveSheet.Name & _
"!__Hilite").RefersTo)
On Error GoTo 0
ActiveSheet.Cells.FormatConditions.Delete
.Names.Add Name:=ActiveSheet.Name & "!__Hilite", _
RefersTo:=Not hilite
.Names(ActiveSheet.Name & "!__Hilite").Visible = False
End With
End Sub

--
HTH

Bob Phillips

"Jim May" <jmay@cox.net> wrote in message
news:%Vqre.49277$Fv.43119@lakeread01...
> Thanks for the code:
> When I click on the Icon (after loading everything) the code "bombs" and
> highlights the word hilite, within ...:=Not hilite.Names(... below..
>
> "!__Hilite", RefersTo:=Not hilite.Names(ActiveSheet.Name &
> "!__Hilite").Visible = False
> End With
> End Sub
>
>
>
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:uKJEAFIcFHA.3808@TK2MSFTNGP14.phx.gbl...
> > Okay Jim,
> >
> > There are 3 parts to this.
> >
> > The first part is just a simple piece of code that creates a toolbar
> button
> > (I am appending it to the format toolbar).
> >
> > The second part is to setup application events, and trap the application
> > sheet selectionchange event and do our highlighting.
> >
> > These first two parts are both included in the ThisWorkbook module, and
> > should be included in Personal.xls.
> >
> > The 3rd part is the macro that is run when the button is clicked, and
> either
> > turns highlighting on the active sheet on or off (by use of a worksheet
> > scope name). SO cliciking once sets highlighting on that page, a second
> time
> > removes it (it could be shift-click to remove if preferred).This goes in
a
> > standard code module, also in Personal.xls.
> >
> > BTW, in my version that I installed with some colleagues, we had row and
> > column highlighting, but only for 5 cells either side. Would you like
this
> > version?
> >
> > >>>>>>>>>>> ThisWorkbook
> >
> > Option Explicit
> >
> > Public WithEvents App As Application
> >
> > Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
> > Range)
> > Dim hilite As Boolean
> > hilite = False
> > On Error Resume Next
> > hilite = Evaluate(Sh.Parent.Names(Sh.Name &
"!__Hilite").RefersTo)
> > On Error GoTo 0
> > If hilite Then
> > Sh.Cells.FormatConditions.Delete
> > With Target.EntireRow
> > .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> > With .FormatConditions(1)
> > With .Borders(xlTop)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = 5
> > End With
> > With .Borders(xlBottom)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = 5
> > End With
> > .Interior.ColorIndex = xlColorIndexNone
> > .Font.ColorIndex = 3
> > .Font.Bold = True
> > End With
> > End With
> > End If
> > End Sub
> >
> > Private Sub Workbook_Open()
> > Dim oCtl As CommandBarControl
> > Set App = Application
> >
> > On Error Resume Next
> > Application.CommandBars("Formatting").Controls("Hilite").Delete
> > On Error GoTo 0
> >
> > With Application.CommandBars("Formatting")
> > Set oCtl = .Controls.Add(Type:=msoControlButton,
Temporary:=True)
> > oCtl.Caption = "Hilite"
> > oCtl.Style = msoButtonIconAndCaption
> > oCtl.FaceId = 340
> > oCtl.OnAction = "SetupHilite"
> > End With
> > End Sub
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > On Error Resume Next
> > Application.CommandBars("Formatting").Controls("Hilite").Delete
> > On Error GoTo 0
> > End Sub
> >
> >
> > >>>>>>>>> Standard code module
> >
> >
> > Public Sub SetupHilite()
> > Dim hilite As Boolean
> > With ActiveWorkbook
> > hilite = False
> > On Error Resume Next
> > hilite = Evaluate(.Names(ActiveSheet.Name &
> > "!__Hilite").RefersTo)
> > On Error GoTo 0
> > ActiveSheet.Cells.FormatConditions.Delete
> > .Names.Add Name:=ActiveSheet.Name & "!__Hilite", RefersTo:=Not
> > hilite
> > .Names(ActiveSheet.Name & "!__Hilite").Visible = False
> > End With
> > End Sub
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Jim May" <jmay@cox.net> wrote in message
> > news:8nnre.49257$Fv.13329@lakeread01...
> > > Do you mean that you would like to have it apply to the activesheet on
> > > demand? YES
> > > and what about if we already have that
> > > event. SHOULD NOT EVER
> > > What I would do is add application events, and setup a sheet dependent
> > name
> > > that is set/unset by the button. HOW IS THIS DONE?
> > > Jim
> > >
> > > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > > news:uzoSueBcFHA.228@TK2MSFTNGP12.phx.gbl...
> > > > Jim,
> > > >
> > > > Do you mean that you would like to have it apply to the activesheet
on
> > > > demand? Problem here is that it is event code so we need to setup
that
> > > > event, remove it on say shift toggle, and what about if we already
> have
> > > that
> > > > event.
> > > >
> > > > What I would do is add application events, and setup a sheet
dependent
> > > name
> > > > that is set/unset by the button.
> > > >
> > > > Sound good?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Jim May" <jmay@cox.net> wrote in message
> > > > news:qGere.49221$Fv.38665@lakeread01...
> > > > > Cool Bob,,
> > > > > I'd like to have this macro available (ON CALL) to apply to
numerous
> > > > > workbooks/worksheets I work with. How would I go about doing
this?
> > > > > Could I put a new button on my toolbar to run the macro;
> > > > > and have some code in my personal.xls; maybe some type
> > > > > of toggle (On/Off) feature...?? of course applying to the
> > Activesheet..
> > > > > TIA,
> > > > > Jim
> > > > >
> > > > > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > > > > news:uv91HtAcFHA.3808@TK2MSFTNGP14.phx.gbl...
> > > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > > Cells.FormatConditions.Delete
> > > > > > With Target.EntireRow
> > > > > > .FormatConditions.Add Type:=xlExpression,
Formula1:="TRUE"
> > > > > > With .FormatConditions(1)
> > > > > > With .Borders(xlTop)
> > > > > > .LineStyle = xlContinuous
> > > > > > .Weight = xlThin
> > > > > > .ColorIndex = 5
> > > > > > End With
> > > > > > With .Borders(xlBottom)
> > > > > > .LineStyle = xlContinuous
> > > > > > .Weight = xlThin
> > > > > > .ColorIndex = 5
> > > > > > End With
> > > > > > .Interior.ColorIndex = xlColorIndexNone
> > > > > > .Font.ColorIndex = 3
> > > > > > .Font.Bold = True
> > > > > > End With
> > > > > > End With
> > > > > >
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > 'This is worksheet event code, which means that it needs to be
> > > > > > 'placed in the appropriate worksheet code module, not a standard
> > > > > > 'code module. To do this, right-click on the sheet tab, select
> > > > > > 'the View Code option from the menu, and paste the code in.
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > "gwenturpin"
> > <gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com>
> > > > > wrote
> > > > > > in message
> > > > news:gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com...
> > > > > > >
> > > > > > >

> > > > > > >
> > > > > > > I am wanting to change the default highlighting when you hover
> > over
> > > a
> > > > > > > cell/row.
> > > > > > >
> > > > > > > E.g. I will be working on a large list of contact details when
> for
> > > > > > > instance I click on a surname I would like the whole row to
jump
> > out
> > > > > > > e.g be in a white box with bold letters coloured red.
> > > > > > >
> > > > > > > Any ideas????
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > gwenturpin
> > > > > >
> > > >
> >
> ------------------------------------------------------------------------
> > > > > > > gwenturpin's Profile:
> > > > > > http://www.excelforum.com/member.php...o&userid=24239
> > > > > > > View this thread:
> > > > > http://www.excelforum.com/showthread...hreadid=378497
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

#14 06-14-2005, 07:05 AM

Perfect Bob,
Will all this (as far as you know) work on Access 97 and up?
Jim

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:OTHI51LcFHA.2180@TK2MSFTNGP12.phx.gbl...
> Wrap-around again Jim. use this
>
> >>>>>>>>> Standard code module
>
>
> Public Sub SetupHilite()
> Dim hilite As Boolean
> With ActiveWorkbook
> hilite = False
> On Error Resume Next
> hilite = Evaluate(.Names(ActiveSheet.Name & _
> "!__Hilite").RefersTo)
> On Error GoTo 0
> ActiveSheet.Cells.FormatConditions.Delete
> .Names.Add Name:=ActiveSheet.Name & "!__Hilite", _
> RefersTo:=Not hilite
> .Names(ActiveSheet.Name & "!__Hilite").Visible = False
> End With
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> "Jim May" <jmay@cox.net> wrote in message
> news:%Vqre.49277$Fv.43119@lakeread01...
> > Thanks for the code:
> > When I click on the Icon (after loading everything) the code "bombs" and
> > highlights the word hilite, within ...:=Not hilite.Names(... below..
> >
> > "!__Hilite", RefersTo:=Not hilite.Names(ActiveSheet.Name &
> > "!__Hilite").Visible = False
> > End With
> > End Sub
> >
> >
> >
> >
> > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > news:uKJEAFIcFHA.3808@TK2MSFTNGP14.phx.gbl...
> > > Okay Jim,
> > >
> > > There are 3 parts to this.
> > >
> > > The first part is just a simple piece of code that creates a toolbar
> > button
> > > (I am appending it to the format toolbar).
> > >
> > > The second part is to setup application events, and trap the
application
> > > sheet selectionchange event and do our highlighting.
> > >
> > > These first two parts are both included in the ThisWorkbook module,
and
> > > should be included in Personal.xls.
> > >
> > > The 3rd part is the macro that is run when the button is clicked, and
> > either
> > > turns highlighting on the active sheet on or off (by use of a
worksheet
> > > scope name). SO cliciking once sets highlighting on that page, a
second
> > time
> > > removes it (it could be shift-click to remove if preferred).This goes
in
> a
> > > standard code module, also in Personal.xls.
> > >
> > > BTW, in my version that I installed with some colleagues, we had row
and
> > > column highlighting, but only for 5 cells either side. Would you like
> this
> > > version?
> > >
> > > >>>>>>>>>>> ThisWorkbook
> > >
> > > Option Explicit
> > >
> > > Public WithEvents App As Application
> > >
> > > Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As
> > > Range)
> > > Dim hilite As Boolean
> > > hilite = False
> > > On Error Resume Next
> > > hilite = Evaluate(Sh.Parent.Names(Sh.Name &
> "!__Hilite").RefersTo)
> > > On Error GoTo 0
> > > If hilite Then
> > > Sh.Cells.FormatConditions.Delete
> > > With Target.EntireRow
> > > .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> > > With .FormatConditions(1)
> > > With .Borders(xlTop)
> > > .LineStyle = xlContinuous
> > > .Weight = xlThin
> > > .ColorIndex = 5
> > > End With
> > > With .Borders(xlBottom)
> > > .LineStyle = xlContinuous
> > > .Weight = xlThin
> > > .ColorIndex = 5
> > > End With
> > > .Interior.ColorIndex = xlColorIndexNone
> > > .Font.ColorIndex = 3
> > > .Font.Bold = True
> > > End With
> > > End With
> > > End If
> > > End Sub
> > >
> > > Private Sub Workbook_Open()
> > > Dim oCtl As CommandBarControl
> > > Set App = Application
> > >
> > > On Error Resume Next
> > >
Application.CommandBars("Formatting").Controls("Hilite").Delete
> > > On Error GoTo 0
> > >
> > > With Application.CommandBars("Formatting")
> > > Set oCtl = .Controls.Add(Type:=msoControlButton,
> Temporary:=True)
> > > oCtl.Caption = "Hilite"
> > > oCtl.Style = msoButtonIconAndCaption
> > > oCtl.FaceId = 340
> > > oCtl.OnAction = "SetupHilite"
> > > End With
> > > End Sub
> > >
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > On Error Resume Next
> > >
Application.CommandBars("Formatting").Controls("Hilite").Delete
> > > On Error GoTo 0
> > > End Sub
> > >
> > >
> > > >>>>>>>>> Standard code module
> > >
> > >
> > > Public Sub SetupHilite()
> > > Dim hilite As Boolean
> > > With ActiveWorkbook
> > > hilite = False
> > > On Error Resume Next
> > > hilite = Evaluate(.Names(ActiveSheet.Name &
> > > "!__Hilite").RefersTo)
> > > On Error GoTo 0
> > > ActiveSheet.Cells.FormatConditions.Delete
> > > .Names.Add Name:=ActiveSheet.Name & "!__Hilite", RefersTo:=Not
> > > hilite
> > > .Names(ActiveSheet.Name & "!__Hilite").Visible = False
> > > End With
> > > End Sub
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Jim May" <jmay@cox.net> wrote in message
> > > news:8nnre.49257$Fv.13329@lakeread01...
> > > > Do you mean that you would like to have it apply to the activesheet
on
> > > > demand? YES
> > > > and what about if we already have that
> > > > event. SHOULD NOT EVER
> > > > What I would do is add application events, and setup a sheet
dependent
> > > name
> > > > that is set/unset by the button. HOW IS THIS DONE?
> > > > Jim
> > > >
> > > > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > > > news:uzoSueBcFHA.228@TK2MSFTNGP12.phx.gbl...
> > > > > Jim,
> > > > >
> > > > > Do you mean that you would like to have it apply to the
activesheet
> on
> > > > > demand? Problem here is that it is event code so we need to setup
> that
> > > > > event, remove it on say shift toggle, and what about if we already
> > have
> > > > that
> > > > > event.
> > > > >
> > > > > What I would do is add application events, and setup a sheet
> dependent
> > > > name
> > > > > that is set/unset by the button.
> > > > >
> > > > > Sound good?
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "Jim May" <jmay@cox.net> wrote in message
> > > > > news:qGere.49221$Fv.38665@lakeread01...
> > > > > > Cool Bob,,
> > > > > > I'd like to have this macro available (ON CALL) to apply to
> numerous
> > > > > > workbooks/worksheets I work with. How would I go about doing
> this?
> > > > > > Could I put a new button on my toolbar to run the macro;
> > > > > > and have some code in my personal.xls; maybe some type
> > > > > > of toggle (On/Off) feature...?? of course applying to the
> > > Activesheet..
> > > > > > TIA,
> > > > > > Jim
> > > > > >
> > > > > > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > > > > > news:uv91HtAcFHA.3808@TK2MSFTNGP14.phx.gbl...
> > > > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > > > Cells.FormatConditions.Delete
> > > > > > > With Target.EntireRow
> > > > > > > .FormatConditions.Add Type:=xlExpression,
> Formula1:="TRUE"
> > > > > > > With .FormatConditions(1)
> > > > > > > With .Borders(xlTop)
> > > > > > > .LineStyle = xlContinuous
> > > > > > > .Weight = xlThin
> > > > > > > .ColorIndex = 5
> > > > > > > End With
> > > > > > > With .Borders(xlBottom)
> > > > > > > .LineStyle = xlContinuous
> > > > > > > .Weight = xlThin
> > > > > > > .ColorIndex = 5
> > > > > > > End With
> > > > > > > .Interior.ColorIndex = xlColorIndexNone
> > > > > > > .Font.ColorIndex = 3
> > > > > > > .Font.Bold = True
> > > > > > > End With
> > > > > > > End With
> > > > > > >
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > > 'This is worksheet event code, which means that it needs to be
> > > > > > > 'placed in the appropriate worksheet code module, not a
standard
> > > > > > > 'code module. To do this, right-click on the sheet tab, select
> > > > > > > 'the View Code option from the menu, and paste the code in.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > HTH
> > > > > > >
> > > > > > > Bob Phillips
> > > > > > >
> > > > > > > "gwenturpin"
> > > <gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com>
> > > > > > wrote
> > > > > > > in message
> > > > > news:gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com...
> > > > > > > >
> > > > > > > >

> > > > > > > >
> > > > > > > > I am wanting to change the default highlighting when you
hover
> > > over
> > > > a
> > > > > > > > cell/row.
> > > > > > > >
> > > > > > > > E.g. I will be working on a large list of contact details
when
> > for
> > > > > > > > instance I click on a surname I would like the whole row to
> jump
> > > out
> > > > > > > > e.g be in a white box with bold letters coloured red.
> > > > > > > >
> > > > > > > > Any ideas????
> > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > > gwenturpin
> > > > > > >
> > > > >
> > >
> > ------------------------------------------------------------------------
> > > > > > > > gwenturpin's Profile:
> > > > > > >
http://www.excelforum.com/member.php...o&userid=24239
> > > > > > > > View this thread:
> > > > > > http://www.excelforum.com/showthread...hreadid=378497
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

#15 06-14-2005, 11:05 AM

Do you mean Excel 97 and up?

--
HTH

Bob Phillips

"Jim May" <jmay@cox.net> wrote in message
news:hgyre.50319$Fv.19841@lakeread01...
> Perfect Bob,
> Will all this (as far as you know) work on Access 97 and up?
> Jim
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:OTHI51LcFHA.2180@TK2MSFTNGP12.phx.gbl...
> > Wrap-around again Jim. use this
> >
> > >>>>>>>>> Standard code module
> >
> >
> > Public Sub SetupHilite()
> > Dim hilite As Boolean
> > With ActiveWorkbook
> > hilite = False
> > On Error Resume Next
> > hilite = Evaluate(.Names(ActiveSheet.Name & _
> > "!__Hilite").RefersTo)
> > On Error GoTo 0
> > ActiveSheet.Cells.FormatConditions.Delete
> > .Names.Add Name:=ActiveSheet.Name & "!__Hilite", _
> > RefersTo:=Not hilite
> > .Names(ActiveSheet.Name & "!__Hilite").Visible = False
> > End With
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Jim May" <jmay@cox.net> wrote in message
> > news:%Vqre.49277$Fv.43119@lakeread01...
> > > Thanks for the code:
> > > When I click on the Icon (after loading everything) the code "bombs"
and
> > > highlights the word hilite, within ...:=Not hilite.Names(... below..
> > >
> > > "!__Hilite", RefersTo:=Not hilite.Names(ActiveSheet.Name &
> > > "!__Hilite").Visible = False
> > > End With
> > > End Sub
> > >
> > >
> > >
> > >
> > > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > > news:uKJEAFIcFHA.3808@TK2MSFTNGP14.phx.gbl...
> > > > Okay Jim,
> > > >
> > > > There are 3 parts to this.
> > > >
> > > > The first part is just a simple piece of code that creates a toolbar
> > > button
> > > > (I am appending it to the format toolbar).
> > > >
> > > > The second part is to setup application events, and trap the
> application
> > > > sheet selectionchange event and do our highlighting.
> > > >
> > > > These first two parts are both included in the ThisWorkbook module,
> and
> > > > should be included in Personal.xls.
> > > >
> > > > The 3rd part is the macro that is run when the button is clicked,
and
> > > either
> > > > turns highlighting on the active sheet on or off (by use of a
> worksheet
> > > > scope name). SO cliciking once sets highlighting on that page, a
> second
> > > time
> > > > removes it (it could be shift-click to remove if preferred).This
goes
> in
> > a
> > > > standard code module, also in Personal.xls.
> > > >
> > > > BTW, in my version that I installed with some colleagues, we had row
> and
> > > > column highlighting, but only for 5 cells either side. Would you
like
> > this
> > > > version?
> > > >
> > > > >>>>>>>>>>> ThisWorkbook
> > > >
> > > > Option Explicit
> > > >
> > > > Public WithEvents App As Application
> > > >
> > > > Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal
Target
> As
> > > > Range)
> > > > Dim hilite As Boolean
> > > > hilite = False
> > > > On Error Resume Next
> > > > hilite = Evaluate(Sh.Parent.Names(Sh.Name &
> > "!__Hilite").RefersTo)
> > > > On Error GoTo 0
> > > > If hilite Then
> > > > Sh.Cells.FormatConditions.Delete
> > > > With Target.EntireRow
> > > > .FormatConditions.Add Type:=xlExpression,
Formula1:="TRUE"
> > > > With .FormatConditions(1)
> > > > With .Borders(xlTop)
> > > > .LineStyle = xlContinuous
> > > > .Weight = xlThin
> > > > .ColorIndex = 5
> > > > End With
> > > > With .Borders(xlBottom)
> > > > .LineStyle = xlContinuous
> > > > .Weight = xlThin
> > > > .ColorIndex = 5
> > > > End With
> > > > .Interior.ColorIndex = xlColorIndexNone
> > > > .Font.ColorIndex = 3
> > > > .Font.Bold = True
> > > > End With
> > > > End With
> > > > End If
> > > > End Sub
> > > >
> > > > Private Sub Workbook_Open()
> > > > Dim oCtl As CommandBarControl
> > > > Set App = Application
> > > >
> > > > On Error Resume Next
> > > >
> Application.CommandBars("Formatting").Controls("Hilite").Delete
> > > > On Error GoTo 0
> > > >
> > > > With Application.CommandBars("Formatting")
> > > > Set oCtl = .Controls.Add(Type:=msoControlButton,
> > Temporary:=True)
> > > > oCtl.Caption = "Hilite"
> > > > oCtl.Style = msoButtonIconAndCaption
> > > > oCtl.FaceId = 340
> > > > oCtl.OnAction = "SetupHilite"
> > > > End With
> > > > End Sub
> > > >
> > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > On Error Resume Next
> > > >
> Application.CommandBars("Formatting").Controls("Hilite").Delete
> > > > On Error GoTo 0
> > > > End Sub
> > > >
> > > >
> > > > >>>>>>>>> Standard code module
> > > >
> > > >
> > > > Public Sub SetupHilite()
> > > > Dim hilite As Boolean
> > > > With ActiveWorkbook
> > > > hilite = False
> > > > On Error Resume Next
> > > > hilite = Evaluate(.Names(ActiveSheet.Name &
> > > > "!__Hilite").RefersTo)
> > > > On Error GoTo 0
> > > > ActiveSheet.Cells.FormatConditions.Delete
> > > > .Names.Add Name:=ActiveSheet.Name & "!__Hilite",
RefersTo:=Not
> > > > hilite
> > > > .Names(ActiveSheet.Name & "!__Hilite").Visible = False
> > > > End With
> > > > End Sub
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Jim May" <jmay@cox.net> wrote in message
> > > > news:8nnre.49257$Fv.13329@lakeread01...
> > > > > Do you mean that you would like to have it apply to the
activesheet
> on
> > > > > demand? YES
> > > > > and what about if we already have that
> > > > > event. SHOULD NOT EVER
> > > > > What I would do is add application events, and setup a sheet
> dependent
> > > > name
> > > > > that is set/unset by the button. HOW IS THIS DONE?
> > > > > Jim
> > > > >
> > > > > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > > > > news:uzoSueBcFHA.228@TK2MSFTNGP12.phx.gbl...
> > > > > > Jim,
> > > > > >
> > > > > > Do you mean that you would like to have it apply to the
> activesheet
> > on
> > > > > > demand? Problem here is that it is event code so we need to
setup
> > that
> > > > > > event, remove it on say shift toggle, and what about if we
already
> > > have
> > > > > that
> > > > > > event.
> > > > > >
> > > > > > What I would do is add application events, and setup a sheet
> > dependent
> > > > > name
> > > > > > that is set/unset by the button.
> > > > > >
> > > > > > Sound good?
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > "Jim May" <jmay@cox.net> wrote in message
> > > > > > news:qGere.49221$Fv.38665@lakeread01...
> > > > > > > Cool Bob,,
> > > > > > > I'd like to have this macro available (ON CALL) to apply to
> > numerous
> > > > > > > workbooks/worksheets I work with. How would I go about doing
> > this?
> > > > > > > Could I put a new button on my toolbar to run the macro;
> > > > > > > and have some code in my personal.xls; maybe some type
> > > > > > > of toggle (On/Off) feature...?? of course applying to the
> > > > Activesheet..
> > > > > > > TIA,
> > > > > > > Jim
> > > > > > >
> > > > > > > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > > > > > > news:uv91HtAcFHA.3808@TK2MSFTNGP14.phx.gbl...
> > > > > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > > > > Cells.FormatConditions.Delete
> > > > > > > > With Target.EntireRow
> > > > > > > > .FormatConditions.Add Type:=xlExpression,
> > Formula1:="TRUE"
> > > > > > > > With .FormatConditions(1)
> > > > > > > > With .Borders(xlTop)
> > > > > > > > .LineStyle = xlContinuous
> > > > > > > > .Weight = xlThin
> > > > > > > > .ColorIndex = 5
> > > > > > > > End With
> > > > > > > > With .Borders(xlBottom)
> > > > > > > > .LineStyle = xlContinuous
> > > > > > > > .Weight = xlThin
> > > > > > > > .ColorIndex = 5
> > > > > > > > End With
> > > > > > > > .Interior.ColorIndex = xlColorIndexNone
> > > > > > > > .Font.ColorIndex = 3
> > > > > > > > .Font.Bold = True
> > > > > > > > End With
> > > > > > > > End With
> > > > > > > >
> > > > > > > >
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > >
> > > > > > > > 'This is worksheet event code, which means that it needs to
be
> > > > > > > > 'placed in the appropriate worksheet code module, not a
> standard
> > > > > > > > 'code module. To do this, right-click on the sheet tab,
select
> > > > > > > > 'the View Code option from the menu, and paste the code in.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > Bob Phillips
> > > > > > > >
> > > > > > > > "gwenturpin"
> > > > <gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com>
> > > > > > > wrote
> > > > > > > > in message
> > > > > > news:gwenturpin.1qkaic_1118660702.5427@excelforum-nospam.com...
> > > > > > > > >
> > > > > > > > >

> > > > > > > > >
> > > > > > > > > I am wanting to change the default highlighting when you
> hover
> > > > over
> > > > > a
> > > > > > > > > cell/row.
> > > > > > > > >
> > > > > > > > > E.g. I will be working on a large list of contact details
> when
> > > for
> > > > > > > > > instance I click on a surname I would like the whole row
to
> > jump
> > > > out
> > > > > > > > > e.g be in a white box with bold letters coloured red.
> > > > > > > > >
> > > > > > > > > Any ideas????
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > gwenturpin
> > > > > > > >
> > > > > >
> > > >
> >
> ------------------------------------------------------------------------
> > > > > > > > > gwenturpin's Profile:
> > > > > > > >
> http://www.excelforum.com/member.php...o&userid=24239
> > > > > > > > > View this thread:
> > > > > > > http://www.excelforum.com/showthread...hreadid=378497
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Welcome to the Excel Forum

Forum Statistics