Re: Highlighting

Tech-Archive recommends: Fix windows errors by optimizing your registry

Excel 2000 and also XP;
Tks,
Jim


"Bob Phillips" <phillips@xxxxxxxxxxxxx> wrote in message
news:OwPSsogcFHA.2076@xxxxxxxxxxxxxxxxxxxxxxx
> Jim,
>
> I will try and test this tomorrow. Which version of Excel?
>
> --
> HTH
>
> Bob Phillips
>
> "Jim May" <jmay@xxxxxxx> wrote in message
> news:gC2se.51940$Fv.43284@xxxxxxxxxxxxx
> > 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:=Active***.Name & "!__Hilite", _
> > RefersTo:=Not hilite
> >
> >
> > Public Sub SetupHilite()
> > Dim hilite As Boolean
> > With ActiveWorkbook
> > hilite = False
> > On Error Resume Next
> > hilite = Evaluate(.Names(Active***.Name & _
> >
> > "!__Hilite").RefersTo)
> > On Error GoTo 0
> > Active***.Cells.FormatConditions.Delete
> > .Names.Add Name:=Active***.Name & "!__Hilite", _
> > RefersTo:=Not hilite
> > .Names(Active***.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@xxxxxxxxxxxxx> wrote in message
> > news:OTHI51LcFHA.2180@xxxxxxxxxxxxxxxxxxxxxxx
> > > 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(Active***.Name & _
> > >
"!__Hilite").RefersTo)
> > > On Error GoTo 0
> > > Active***.Cells.FormatConditions.Delete
> > > .Names.Add Name:=Active***.Name & "!__Hilite", _
> > > RefersTo:=Not hilite
> > > .Names(Active***.Name & "!__Hilite").Visible = False
> > > End With
> > > End Sub
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Jim May" <jmay@xxxxxxx> wrote in message
> > > news:%Vqre.49277$Fv.43119@xxxxxxxxxxxxx
> > > > 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(Active***.Name &
> > > > "!__Hilite").Visible = False
> > > > End With
> > > > End Sub
> > > >
> > > >
> > > >
> > > >
> > > > "Bob Phillips" <phillips@xxxxxxxxxxxxx> wrote in message
> > > > news:uKJEAFIcFHA.3808@xxxxxxxxxxxxxxxxxxxxxxx
> > > > > 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
> > > > > *** 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 *** on or off (by use of a
> > work***
> > > > > 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(Active***.Name &
> > > > > "!__Hilite").RefersTo)
> > > > > On Error GoTo 0
> > > > > Active***.Cells.FormatConditions.Delete
> > > > > .Names.Add Name:=Active***.Name & "!__Hilite",
> RefersTo:=Not
> > > > > hilite
> > > > > .Names(Active***.Name & "!__Hilite").Visible = False
> > > > > End With
> > > > > End Sub
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "Jim May" <jmay@xxxxxxx> wrote in message
> > > > > news:8nnre.49257$Fv.13329@xxxxxxxxxxxxx
> > > > > > Do you mean that you would like to have it apply to the
> active***
> > 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 ***
> > dependent
> > > > > name
> > > > > > that is set/unset by the button. HOW IS THIS DONE?
> > > > > > Jim
> > > > > >
> > > > > > "Bob Phillips" <phillips@xxxxxxxxxxxxx> wrote in message
> > > > > > news:uzoSueBcFHA.228@xxxxxxxxxxxxxxxxxxxxxxx
> > > > > > > Jim,
> > > > > > >
> > > > > > > Do you mean that you would like to have it apply to the
> > active***
> > > 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 ***
> > > dependent
> > > > > > name
> > > > > > > that is set/unset by the button.
> > > > > > >
> > > > > > > Sound good?
> > > > > > >
> > > > > > > --
> > > > > > > HTH
> > > > > > >
> > > > > > > Bob Phillips
> > > > > > >
> > > > > > > "Jim May" <jmay@xxxxxxx> wrote in message
> > > > > > > news:qGere.49221$Fv.38665@xxxxxxxxxxxxx
> > > > > > > > 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
> > > > > Active***..
> > > > > > > > TIA,
> > > > > > > > Jim
> > > > > > > >
> > > > > > > > "Bob Phillips" <phillips@xxxxxxxxxxxxx> wrote in message
> > > > > > > > news:uv91HtAcFHA.3808@xxxxxxxxxxxxxxxxxxxxxxx
> > > > > > > > > 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 work*** event code, which means that it needs
to
> be
> > > > > > > > > 'placed in the appropriate work*** code module, not a
> > standard
> > > > > > > > > 'code module. To do this, right-click on the *** tab,
> select
> > > > > > > > > 'the View Code option from the menu, and paste the code
in.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > HTH
> > > > > > > > >
> > > > > > > > > Bob Phillips
> > > > > > > > >
> > > > > > > > > "gwenturpin"
> > > > > <gwenturpin.1qkaic_1118660702.5427@xxxxxxxxxxxxxxxxxxxxx>
> > > > > > > > wrote
> > > > > > > > > in message
> > > > > > >
news:gwenturpin.1qkaic_1118660702.5427@xxxxxxxxxxxxxxxxxxxxxxxx
> > > > > > > > > >
> > > > > > > > > > :confused:
> > > > > > > > > >
> > > > > > > > > > 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?action=getinfo&userid=24239
> > > > > > > > > > View this thread:
> > > > > > > > http://www.excelforum.com/showthread.php?threadid=378497
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


.

Quantcast