highlight activecell

F

flow23

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

Bob Phillips

Option Explicit

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
With .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 = 20
End With
End With

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.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)
 
F

flow23

for some reason.. nothing is happening??
I have Excel 2000. would that make any difference?
 
B

Bob Phillips

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

Peter Rooney

Bob,

I attempted a version of this that would highlight both active row AND
column, but I get the message "Application defined or Object defined error"
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:="TRUE"
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:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 20
End With
'-------------------------------------------------------------------------
With Target.EntireColumn
* .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
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:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 20
End With

End Sub
 
B

Bob Phillips

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:="TRUE"
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:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 20
End With

'-------------------------------------------------------------------------
With Target.EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
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:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 20
End With

With Target
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
C

CLR

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 ("Edit Me")
End With
End Sub

Vaya con Dios,
Chuck, CABGx3
 
B

Bob Phillips

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

Peter Rooney

Bob,

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

Pete
 
Top