software

I have huge database, which other colleagues view regularly.

and I wondered if there is a way to highlight the activerow to make it easy
to read?
Option Explicit

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
With .EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
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 = 20
End With
End With

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
.FormatConditions(1).Interior.ColorIndex = 36
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

(remove nothere from email address if mailing direct)

quot;flow23quot; gt; wrote in message
...
gt; I have huge database, which other colleagues view regularly.
gt;
gt; and I wondered if there is a way to highlight the activerow to make it
easy
gt; to read?
gt;
gt;
for some reason.. nothing is happening??
I have Excel 2000. would that make any difference?quot;Bob Phillipsquot; wrote:

gt; Option Explicit
gt;
gt; '----------------------------------------------------------------
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; '----------------------------------------------------------------
gt; Cells.FormatConditions.Delete
gt; With Target
gt; With .EntireRow
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; With .FormatConditions(1)
gt; With .Borders(xlTop)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 5
gt; End With
gt; With .Borders(xlBottom)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 5
gt; End With
gt; .Interior.ColorIndex = 20
gt; End With
gt; End With
gt;
gt; .FormatConditions.Delete
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; .FormatConditions(1).Interior.ColorIndex = 36
gt; End With
gt;
gt; End Sub
gt;
gt; 'This is worksheet event code, which means that it needs to be
gt; 'placed in the appropriate worksheet code module, not a standard
gt; 'code module. To do this, right-click on the sheet tab, select
gt; 'the View Code option from the menu, and paste the code in.
gt;
gt;
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;flow23quot; gt; wrote in message
gt; ...
gt; gt; I have huge database, which other colleagues view regularly.
gt; gt;
gt; gt; and I wondered if there is a way to highlight the activerow to make it
gt; easy
gt; gt; to read?
gt; gt;
gt; gt;
gt;
gt;
gt;

No, it shouldn't. Did you put it in the sheet code module as instructed?

Go to the immediate window and type

Application.EnableEvents = True

and try again.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;flow23quot; gt; wrote in message
...
gt; for some reason.. nothing is happening??
gt; I have Excel 2000. would that make any difference?
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Option Explicit
gt; gt;
gt; gt; '----------------------------------------------------------------
gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt; '----------------------------------------------------------------
gt; gt; Cells.FormatConditions.Delete
gt; gt; With Target
gt; gt; With .EntireRow
gt; gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; gt; With .FormatConditions(1)
gt; gt; With .Borders(xlTop)
gt; gt; .LineStyle = xlContinuous
gt; gt; .Weight = xlThin
gt; gt; .ColorIndex = 5
gt; gt; End With
gt; gt; With .Borders(xlBottom)
gt; gt; .LineStyle = xlContinuous
gt; gt; .Weight = xlThin
gt; gt; .ColorIndex = 5
gt; gt; End With
gt; gt; .Interior.ColorIndex = 20
gt; gt; End With
gt; gt; End With
gt; gt;
gt; gt; .FormatConditions.Delete
gt; gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; gt; .FormatConditions(1).Interior.ColorIndex = 36
gt; gt; End With
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; 'This is worksheet event code, which means that it needs to be
gt; gt; 'placed in the appropriate worksheet code module, not a standard
gt; gt; 'code module. To do this, right-click on the sheet tab, select
gt; gt; 'the View Code option from the menu, and paste the code in.
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;flow23quot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I have huge database, which other colleagues view regularly.
gt; gt; gt;
gt; gt; gt; and I wondered if there is a way to highlight the activerow to make it
gt; gt; easy
gt; gt; gt; to read?
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
thanks
I found out.. I put it in workbook.. not worksheet
many thanksquot;flow23quot; wrote:

gt; I have huge database, which other colleagues view regularly.
gt;
gt; and I wondered if there is a way to highlight the activerow to make it easy
gt; to read?
gt;
gt;

Bob,

I attempted a version of this that would highlight both active row AND
column, but I get the message quot;Application defined or Object defined errorquot;
at the line marked with an asterisk.
Any idea why?
Thanks in advance
Pete

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 10
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 10
End With
End With
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
.FormatConditions(1).Interior.ColorIndex = 20
End With
'-------------------------------------------------------------------------
With Target.EntireColumn
* .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 10
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 10
End With
End With
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
.FormatConditions(1).Interior.ColorIndex = 20
End With

End Subquot;Bob Phillipsquot; wrote:

gt; Option Explicit
gt;
gt; '----------------------------------------------------------------
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; '----------------------------------------------------------------
gt; Cells.FormatConditions.Delete
gt; With Target
gt; With .EntireRow
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; With .FormatConditions(1)
gt; With .Borders(xlTop)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 5
gt; End With
gt; With .Borders(xlBottom)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 5
gt; End With
gt; .Interior.ColorIndex = 20
gt; End With
gt; End With
gt;
gt; .FormatConditions.Delete
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; .FormatConditions(1).Interior.ColorIndex = 36
gt; End With
gt;
gt; End Sub
gt;
gt; 'This is worksheet event code, which means that it needs to be
gt; 'placed in the appropriate worksheet code module, not a standard
gt; 'code module. To do this, right-click on the sheet tab, select
gt; 'the View Code option from the menu, and paste the code in.
gt;
gt;
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;flow23quot; gt; wrote in message
gt; ...
gt; gt; I have huge database, which other colleagues view regularly.
gt; gt;
gt; gt; and I wondered if there is a way to highlight the activerow to make it
gt; easy
gt; gt; to read?
gt; gt;
gt; gt;
gt;
gt;
gt;

Hi Peter,

Before adding conditions to the column, you have to delete any existing
conditions (the intersect will have one), then it works. It also looks
better IMO to highlight the activecell differently when you do both row and
column

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 10
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 10
End With
End With
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
.FormatConditions(1).Interior.ColorIndex = 20
End With

'-------------------------------------------------------------------------
With Target.EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 10
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 10
End With
End With
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
.FormatConditions(1).Interior.ColorIndex = 20
End With

With Target
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
.FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Peter Rooneyquot; gt; wrote in message
...
gt; Bob,
gt;
gt; I attempted a version of this that would highlight both active row AND
gt; column, but I get the message quot;Application defined or Object defined
errorquot;
gt; at the line marked with an asterisk.
gt; Any idea why?
gt; Thanks in advance
gt; Pete
gt;
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; Cells.FormatConditions.Delete
gt; With Target.EntireRow
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; With .FormatConditions(1)
gt; With .Borders(xlTop)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 10
gt; End With
gt; With .Borders(xlBottom)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 10
gt; End With
gt; End With
gt; .FormatConditions.Delete
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; .FormatConditions(1).Interior.ColorIndex = 20
gt; End With
gt;
'-------------------------------------------------------------------------
gt; With Target.EntireColumn
gt; * .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; With .FormatConditions(1)
gt; With .Borders(xlLeft)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 10
gt; End With
gt; With .Borders(xlRight)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 10
gt; End With
gt; End With
gt; .FormatConditions.Delete
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; .FormatConditions(1).Interior.ColorIndex = 20
gt; End With
gt;
gt; End Sub
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Option Explicit
gt; gt;
gt; gt; '----------------------------------------------------------------
gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt; '----------------------------------------------------------------
gt; gt; Cells.FormatConditions.Delete
gt; gt; With Target
gt; gt; With .EntireRow
gt; gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; gt; With .FormatConditions(1)
gt; gt; With .Borders(xlTop)
gt; gt; .LineStyle = xlContinuous
gt; gt; .Weight = xlThin
gt; gt; .ColorIndex = 5
gt; gt; End With
gt; gt; With .Borders(xlBottom)
gt; gt; .LineStyle = xlContinuous
gt; gt; .Weight = xlThin
gt; gt; .ColorIndex = 5
gt; gt; End With
gt; gt; .Interior.ColorIndex = 20
gt; gt; End With
gt; gt; End With
gt; gt;
gt; gt; .FormatConditions.Delete
gt; gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; gt; .FormatConditions(1).Interior.ColorIndex = 36
gt; gt; End With
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; 'This is worksheet event code, which means that it needs to be
gt; gt; 'placed in the appropriate worksheet code module, not a standard
gt; gt; 'code module. To do this, right-click on the sheet tab, select
gt; gt; 'the View Code option from the menu, and paste the code in.
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;flow23quot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I have huge database, which other colleagues view regularly.
gt; gt; gt;
gt; gt; gt; and I wondered if there is a way to highlight the activerow to make it
gt; gt; easy
gt; gt; gt; to read?
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
I tried Bob's code straight away and it worked super fine......

Here's a little variation to highlight the cell in a different way.....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.ClearComments
With Target
.AddComment.Visible = True
.Comment.Text (quot;Edit Mequot;)
End With
End Sub

Vaya con Dios,
Chuck, CABGx3
quot;flow23quot; gt; wrote in message
...
gt; I have huge database, which other colleagues view regularly.
gt;
gt; and I wondered if there is a way to highlight the activerow to make it
easy
gt; to read?
gt;
gt;
Reply, that's a good idea for another post I answered, where the guy wanted
formula cells highlighted.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;CLRquot; gt; wrote in message
...
gt; I tried Bob's code straight away and it worked super fine......
gt;
gt; Here's a little variation to highlight the cell in a different way.....
gt;
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; Cells.ClearComments
gt; With Target
gt; .AddComment.Visible = True
gt; .Comment.Text (quot;Edit Mequot;)
gt; End With
gt; End Sub
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;flow23quot; gt; wrote in message
gt; ...
gt; gt; I have huge database, which other colleagues view regularly.
gt; gt;
gt; gt; and I wondered if there is a way to highlight the activerow to make it
gt; easy
gt; gt; to read?
gt; gt;
gt; gt;
gt;
gt;
Bob,

That works fine - thanks for responding to me interrupting someone else's
thread!

Pete

quot;Bob Phillipsquot; wrote:

gt; Hi Peter,
gt;
gt; Before adding conditions to the column, you have to delete any existing
gt; conditions (the intersect will have one), then it works. It also looks
gt; better IMO to highlight the activecell differently when you do both row and
gt; column
gt;
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; Cells.FormatConditions.Delete
gt; With Target.EntireRow
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; With .FormatConditions(1)
gt; With .Borders(xlTop)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 10
gt; End With
gt; With .Borders(xlBottom)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 10
gt; End With
gt; End With
gt; .FormatConditions.Delete
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; .FormatConditions(1).Interior.ColorIndex = 20
gt; End With
gt;
gt; '-------------------------------------------------------------------------
gt; With Target.EntireColumn
gt; .FormatConditions.Delete
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; With .FormatConditions(1)
gt; With .Borders(xlLeft)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 10
gt; End With
gt; With .Borders(xlRight)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 10
gt; End With
gt; End With
gt; .FormatConditions.Delete
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; .FormatConditions(1).Interior.ColorIndex = 20
gt; End With
gt;
gt; With Target
gt; .FormatConditions.Delete
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; .FormatConditions(1).Interior.ColorIndex = 36
gt; End With
gt;
gt; End Sub
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Peter Rooneyquot; gt; wrote in message
gt; ...
gt; gt; Bob,
gt; gt;
gt; gt; I attempted a version of this that would highlight both active row AND
gt; gt; column, but I get the message quot;Application defined or Object defined
gt; errorquot;
gt; gt; at the line marked with an asterisk.
gt; gt; Any idea why?
gt; gt; Thanks in advance
gt; gt; Pete
gt; gt;
gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt; Cells.FormatConditions.Delete
gt; gt; With Target.EntireRow
gt; gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; gt; With .FormatConditions(1)
gt; gt; With .Borders(xlTop)
gt; gt; .LineStyle = xlContinuous
gt; gt; .Weight = xlThin
gt; gt; .ColorIndex = 10
gt; gt; End With
gt; gt; With .Borders(xlBottom)
gt; gt; .LineStyle = xlContinuous
gt; gt; .Weight = xlThin
gt; gt; .ColorIndex = 10
gt; gt; End With
gt; gt; End With
gt; gt; .FormatConditions.Delete
gt; gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; gt; .FormatConditions(1).Interior.ColorIndex = 20
gt; gt; End With
gt; gt;
gt; '-------------------------------------------------------------------------
gt; gt; With Target.EntireColumn
gt; gt; * .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; gt; With .FormatConditions(1)
gt; gt; With .Borders(xlLeft)
gt; gt; .LineStyle = xlContinuous
gt; gt; .Weight = xlThin
gt; gt; .ColorIndex = 10
gt; gt; End With
gt; gt; With .Borders(xlRight)
gt; gt; .LineStyle = xlContinuous
gt; gt; .Weight = xlThin
gt; gt; .ColorIndex = 10
gt; gt; End With
gt; gt; End With
gt; gt; .FormatConditions.Delete
gt; gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; gt; .FormatConditions(1).Interior.ColorIndex = 20
gt; gt; End With
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt;
gt; gt; gt; '----------------------------------------------------------------
gt; gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt; gt; '----------------------------------------------------------------
gt; gt; gt; Cells.FormatConditions.Delete
gt; gt; gt; With Target
gt; gt; gt; With .EntireRow
gt; gt; gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; gt; gt; With .FormatConditions(1)
gt; gt; gt; With .Borders(xlTop)
gt; gt; gt; .LineStyle = xlContinuous
gt; gt; gt; .Weight = xlThin
gt; gt; gt; .ColorIndex = 5
gt; gt; gt; End With
gt; gt; gt; With .Borders(xlBottom)
gt; gt; gt; .LineStyle = xlContinuous
gt; gt; gt; .Weight = xlThin
gt; gt; gt; .ColorIndex = 5
gt; gt; gt; End With
gt; gt; gt; .Interior.ColorIndex = 20
gt; gt; gt; End With
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; .FormatConditions.Delete
gt; gt; gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; gt; gt; .FormatConditions(1).Interior.ColorIndex = 36
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; 'This is worksheet event code, which means that it needs to be
gt; gt; gt; 'placed in the appropriate worksheet code module, not a standard
gt; gt; gt; 'code module. To do this, right-click on the sheet tab, select
gt; gt; gt; 'the View Code option from the menu, and paste the code in.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;flow23quot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; I have huge database, which other colleagues view regularly.
gt; gt; gt; gt;
gt; gt; gt; gt; and I wondered if there is a way to highlight the activerow to make it
gt; gt; gt; easy
gt; gt; gt; gt; to read?
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;