About Us

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

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

--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378497

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

Compile error:
expected end sub

--
gwenturpin
------------------------------------------------------------------------
gwenturpin's Profile: http://www.excelforum.com/member.php...o&userid=24239
View this thread: http://www.excelforum.com/showthread...hreadid=378497

Thanks you its worked!!!

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

--
gwenturpin
------------------------------------------------------------------------
gwenturpin's Profile: http://www.excelforum.com/member.php...o&userid=24239
View this thread: http://www.excelforum.com/showthread...hreadid=378497

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" wrote
in message ...

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

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

--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378497

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" wrote
in message ...

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

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" wrote in message
...
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"
wrote
in message ...

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

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" 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" wrote in message
...
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"
wrote
in message
...

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

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" wrote in message
...
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" 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" wrote in message
...
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"
wrote
in message
...

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

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("Hi lite").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("Hi lite").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" 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" wrote in message
...
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" 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" wrote in message
...
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"

wrote
in message
...

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

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" wrote in message
...
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("Hi lite").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("Hi lite").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" 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" wrote in message
...
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" 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" wrote in message
...
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"

wrote
in message
...

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

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" 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" wrote in message
...
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("Hi lite").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("Hi lite").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" 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" wrote in message
...
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" 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" wrote in message
...
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"

wrote
in message
...

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

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

"Bob Phillips" wrote in message
...
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" 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" wrote in message
...
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("Hi lite").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("Hi lite").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" 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" wrote in message
...
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" 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" wrote in message
...
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"

wrote
in message
...

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

Do you mean Excel 97 and up?

--
HTH

Bob Phillips

"Jim May" 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" wrote in message
...
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" 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" wrote in message
...
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("Hi lite").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("Hi lite").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" 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" wrote in message
...
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" 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" wrote in message
...
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"

wrote
in message
...

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

Bob,
I have no reasonable explanation for why I wrote Access,
when I meant Excel all the time. I installed the multiple macros
on my office computer today. There is only one shortcoming
to the existing code. If you use it in a file that already (previously)
has conditional formatting applied in certain cells, you lose it.

As a generic (one-button) solution, it's still pretty-hard to beat.
Just have to remember not to use it on a sheet that needs and
has existing already conditional formatting through out the sheet.

Thanks,
Jim

"Bob Phillips" wrote in message
...
Do you mean Excel 97 and up?

--
HTH

Bob Phillips

"Jim May" 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" wrote in message
...
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" 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" wrote in message
...
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("Hi lite").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("Hi lite").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" 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" wrote in message
...
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" 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" wrote in message
...
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"

wrote
in message

...

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

Hi Jim,

Yeah, that is its drawback. I could be more specific and only delete it from
previously applied cells, but it could still delete existing CF.

I once tried to adapt it to look for existing CF and add to that, but it got
very complex, and if it is already using 3 conditions, there is nowhere to
go. Probably best to test if there are any conditions when it is being
configured on a sheet and ask for confirmation. If it becomes a problem, as
you say, best not to use it (at least it is configurable by sheet <g), or
use an alternative like Chip Pearson's RowlIner addin which seems to create
a shape which it overlays.

Regards

Bob
"Jim May" wrote in message
news:GfIre.50555$Fv.11058@lakeread01...
Bob,
I have no reasonable explanation for why I wrote Access,
when I meant Excel all the time. I installed the multiple macros
on my office computer today. There is only one shortcoming
to the existing code. If you use it in a file that already (previously)
has conditional formatting applied in certain cells, you lose it.

As a generic (one-button) solution, it's still pretty-hard to beat.
Just have to remember not to use it on a sheet that needs and
has existing already conditional formatting through out the sheet.

Thanks,
Jim

"Bob Phillips" wrote in message
...
Do you mean Excel 97 and up?

--
HTH

Bob Phillips

"Jim May" 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" wrote in message
...
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" 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" wrote in message
...
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("Hi lite").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("Hi lite").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" 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" wrote in message
...
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" 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" wrote in message
...
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"

wrote
in message

...

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

gwenturpin Wrote:
Thanks you its worked!!!

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

I got the same error message (end sub expected or something), how did
you solve it? thanks
Anna

--
annavu
------------------------------------------------------------------------
annavu's Profile: http://www.excelforum.com/member.php...o&userid=24335
View this thread: http://www.excelforum.com/showthread...hreadid=378497

I missed the end sub off the bottom!

--
gwenturpin
------------------------------------------------------------------------
gwenturpin's Profile: http://www.excelforum.com/member.php...o&userid=24239
View this thread: http://www.excelforum.com/showthread...hreadid=378497

Bob:
At work after installing the 3 ThisWorkbook macros and the1 macro (below) in
a standard module, all in my personal.xls file - the combined seems to work
fine,, (at first)).. But then, let's say I open a file (without any
Conditional formatting) but it does have auto-filtering engaged (shouldn't
matter it seems), but anyway when I click on the icon button in my toolbar
"Hilite", I get

RUN/TIME Error 1004
That name is not valid

When I click on debug the 4th line from the bottom is highlighted.

..Names.Add Name:=ActiveSheet.Name & "!__Hilite", _
RefersTo:=Not hilite

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

Afterwards it seems that the button is "dead" - without life on any other
files I later bring up...
Any ideas as to what is causing this?
TIA,
Jim May

"Bob Phillips" wrote in message
...
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" 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" wrote in message
...
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("Hi lite").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("Hi lite").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" 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" wrote in message
...
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" 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" wrote in message
...
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"

wrote
in message
...

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

Jim,

I will try and test this tomorrow. Which version of Excel?

--
HTH

Bob Phillips

"Jim May" wrote in message
news:gC2se.51940$Fv.43284@lakeread01...
Bob:
At work after installing the 3 ThisWorkbook macros and the1 macro (below)
in
a standard module, all in my personal.xls file - the combined seems to
work
fine,, (at first)).. But then, let's say I open a file (without any
Conditional formatting) but it does have auto-filtering engaged (shouldn't
matter it seems), but anyway when I click on the icon button in my toolbar
"Hilite", I get

RUN/TIME Error 1004
That name is not valid

When I click on debug the 4th line from the bottom is highlighted.

.Names.Add Name:=ActiveSheet.Name & "!__Hilite", _
RefersTo:=Not hilite

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

Afterwards it seems that the button is "dead" - without life on any other
files I later bring up...
Any ideas as to what is causing this?
TIA,
Jim May

"Bob Phillips" wrote in message
...
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" 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" wrote in message
...
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("Hi lite").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("Hi lite").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" 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" wrote in message
...
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" 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" wrote in message
...
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"

wrote
in message
...

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

Excel 2000 and also XP;
Tks,
Jim

"Bob Phillips" wrote in message
...
Jim,

I will try and test this tomorrow. Which version of Excel?

--
HTH

Bob Phillips

"Jim May" wrote in message
news:gC2se.51940$Fv.43284@lakeread01...
Bob:
At work after installing the 3 ThisWorkbook macros and the1 macro
(below)
in
a standard module, all in my personal.xls file - the combined seems to
work
fine,, (at first)).. But then, let's say I open a file (without any
Conditional formatting) but it does have auto-filtering engaged
(shouldn't
matter it seems), but anyway when I click on the icon button in my
toolbar
"Hilite", I get

RUN/TIME Error 1004
That name is not valid

When I click on debug the 4th line from the bottom is highlighted.

.Names.Add Name:=ActiveSheet.Name & "!__Hilite", _
RefersTo:=Not hilite

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

Afterwards it seems that the button is "dead" - without life on any
other
files I later bring up...
Any ideas as to what is causing this?
TIA,
Jim May

"Bob Phillips" wrote in message
...
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" 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" wrote in message
...
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("Hi lite").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("Hi lite").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" 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" wrote in message
...
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" 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" wrote in message
...
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"

wrote
in message

...

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

Can you give me some guidance on the borders and the different
variations taht you can have

Pank Wrote:
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"
wrote
in message
...

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

--
gwenturpin
------------------------------------------------------------------------
gwenturpin's Profile: http://www.excelforum.com/member.php...o&userid=24239
View this thread: http://www.excelforum.com/showthread...hreadid=378497

Tons, different border styles (dotted, hyphenated, solid), border colours,
cell colours. You can also have the active cell a different colour to the
rest of the row. You can have column as well as row, whole column/row, or
just a few cells. Enough to play with.

--
HTH

Bob Phillips

"gwenturpin" wrote
in message ...

Can you give me some guidance on the borders and the different
variations taht you can have

Pank Wrote:
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"
wrote
in message
...

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

--
gwenturpin
------------------------------------------------------------------------
gwenturpin's Profile:
http://www.excelforum.com/member.php...o&userid=24239
View this thread: http://www.excelforum.com/showthread...hreadid=378497

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Highlighting rows or cells	CP	Excel Discussion (Misc queries)	3	May 23rd 05 11:03 PM
Help with Highlighting all duplicates in a row	Jimv	Excel Discussion (Misc queries)	4	April 21st 05 07:12 PM
highlighting cells which have dates later than...	Rory Carroll	Excel Discussion (Misc queries)	6	February 3rd 05 11:39 PM
Comparing data in two columns and highlighting the data	David Kinsley	Excel Worksheet Functions	6	January 4th 05 06:01 PM
Highlighting positive and negative changes	Dave	Excel Discussion (Misc queries)	1	December 27th 04 10:46 PM

Menu

About Us