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