highlight row

B

Benjamin

Is there any way to have excel automatically highlight the row of a selected
cell ?

TIA

Ben
 
B

Bob Phillips

'----------------------------------------------------------------
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
With .EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlRight)
.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

RP
(remove nothere from the email address if mailing direct)
 
B

Benjamin

Thanks Bob! Two issues though. Any way to highlight only the row and not
the column? And how could I limit this code to execute only within a specific
range?

Ben
 
B

Bob Phillips

Ben,

Here is an alternate. It test for rows 2:20, which you can adjust to suit

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
If Not Intersect(Target, Range("2:20")) Is Nothing Then
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 If
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Benjamin

Works perfect. Thanks again.

Bob Phillips said:
Ben,

Here is an alternate. It test for rows 2:20, which you can adjust to suit

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
If Not Intersect(Target, Range("2:20")) Is Nothing Then
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 If
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
B

Benjamin

I think I spoke too soon. This works fine, except it seems to interfere with
a conditional format I have outside of the area I have the macro working.
Conditional format is based on values wihtin the area the macro runs. Any
ideas?
 
B

Bob Phillips

If you already have a conditional format, this is problemmatical. I once
tried to work it to add a condition, but it was slow and convoluted.

An alternative is to use normal colouring, as shown below. This will wipe
out any standard colouring or borders that you may have, so if this is a
problem another way is to add a shape over the row as Chip does here
http://www.cpearson.com/excel/RowLiner.htm

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
With Cells
With .Borders(xlTop)
.LineStyle = xlLineStyleNone
.ColorIndex = xlColorIndexNone
End With
With .Borders(xlBottom)
.LineStyle = xlLineStyleNone
.ColorIndex = xlColorIndexNone
End With
.Interior.ColorIndex = xlColorIndexNone
End With
If Not Intersect(Target, Range("2:20")) Is Nothing Then
With Target
With .EntireRow
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

.Interior.ColorIndex = 36
End With
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top