Thread: Highlighting

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" <phillips@tiscali.co.uk> wrote in message
news:%23fcQD4OcFHA.228@TK2MSFTNGP12.phx.gbl...
> 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
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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" <jmay@cox.net> 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" <phillips@tiscali.co.uk> wrote in message
> news:%23fcQD4OcFHA.228@TK2MSFTNGP12.phx.gbl...
> > 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
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Originally Posted by gwenturpin

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

I missed the end sub off the bottom!

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" <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
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Jim,

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

--
HTH

Bob Phillips

"Jim May" <jmay@cox.net> 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" <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
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Excel 2000 and also XP;
Tks,
Jim


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:OwPSsogcFHA.2076@TK2MSFTNGP15.phx.gbl...
> Jim,
>
> I will try and test this tomorrow. Which version of Excel?
>
> --
> HTH
>
> Bob Phillips
>
> "Jim May" <jmay@cox.net> 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" <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
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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

Originally Posted by Pank

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
> >
>
>
>

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" <gwenturpin.1qpxab_1118923510.8284@excelforum-nospam.com> wrote
in message news:gwenturpin.1qpxab_1118923510.8284@excelforum-nospam.com...
>
> 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"
> > <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
> > > >
> > >
> > >
> > >
>
>
> --
> gwenturpin
> ------------------------------------------------------------------------
> gwenturpin's Profile:
http://www.excelforum.com/member.php...o&userid=24239
> View this thread: http://www.excelforum.com/showthread...hreadid=378497
>