Highlighting

[gwenturpin]

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

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

Any ideas????

 

[mangesh_yadav]

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

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


End Sub


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

Mangesh

 

[gwenturpin]

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

Compile error:
expected end sub

 

[gwenturpin]

Thanks you its worked!!!

Can I make the text bigger or have the column headings a differen
colou

 

[Bob Phillips]

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.

 

[mangesh_yadav]

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 a
you wish.

Manges

 

[Guest]

Gwenturpin

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

Regards

Pank

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.

 

[Jim May]

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

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

in message news:[email protected]...

 

[Bob Phillips]

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?

 

[Jim May]

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]

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?

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



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

 

[Jim May]

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]

Wrap-around again Jim. use this


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

 

[Jim May]

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

 

[Bob Phillips]

Do you mean Excel 97 and up?

 

[Jim May]

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]

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

 

[annavu]

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

 

[gwenturpin]

I missed the end sub off the bottom

 

[Jim May]

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