Moving highlight

[Michael]

Hi ,
Is there a way to place a horizontal line accross a spread and have it step
from row to row?
I have a growing spread sheet with columns A-T and this would make reading
it a lot easier.
Thanks
Michael

 

[Gord Dibben]

Maybe select B2 and Window>Freeze Panes to keep row 1 and column A in view
as you scroll?

Or maybe Chip Pearson's ROWLINER add-in to highlight the row and column that
is active?

http://www.cpearson.com/excel/RowLiner.htm


Gord Dibben MS Excel MVP

 

[Bob Phillips]

One way


'----------------------------------------------------------------
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.

 

[LP]

Maybe select B2 and Window>Freeze Panes to keep row 1 and column A in view
as you scroll?

Or maybe Chip Pearson's ROWLINER add-in to highlight the row and column that
is active?

http://www.cpearson.com/excel/RowLiner.htm

Gord Dibben  MS Excel MVP



- Show quoted text -

Michael,

You can find more information on "banding" here:
http://groups.google.com/group/micr...n&group=microsoft.public.excel.misc&q=banding

 

[Dan the Man]

This is a great code. Is there a way to only highlight the row without
highlighting the column? And how would one change the color of the highlight?
I'm assuming it is the reference to "ColorIndex", but I'm not clear which
numbers represent which colors.

Thanks!

 

[Bob Phillips]

The change is straight-forward enough, and this code should help changing
the colours

Public Enum xlColorIndex
xlCIBlack = 1
xlCIWhite = 2
xlCIRed = 3
xlCIBrightGreen = 4
xlCIBlue = 5
xlCIYellow = 6
xlCIPink = 7
xlCITurquoise = 8
xlCIDarkRed = 9
xlCIGreen = 10
xlCIDarkBlue = 11
xlCIDarkYellow = 12
xlCIViolet = 13
xlCITeal = 14
xlCIGray25 = 15
xlCIGray50 = 16
xlCIPeriwinkle = 17 '-----------------------------
xlCIPlum = 18 ' chart colours
xlCIIvory = 19 '
xlCILightTurquoise = 20 '
xlCIDarkPurple = 21 '
xlCIChartCoral = 22 '
xlCIChartOceanBlue = 23 '
xlCIChartIceBlue = 24 '
xlCIChartDarkBlue = 25 '
xlCIChartPink = 26 '
xlCIChartYellow = 27 '
xlCIChartTurquoise = 28 '
xlCIChartViolet = 29 '
xlCIChartDarkRed = 30 '
xlCIChartTeal = 31 '
xlCIChartBlue = 32 '-----------------------------
xlCISkyBlue = 33
xlCILightGreen = 35
xlCILightYellow = 36
xlCIPaleBlue = 37
xlCIRose = 38
xlCILavender = 39
xlCITan = 40
xlCILightBlue = 41
xlCIAqua = 42
xlCILime = 43
xlCIGold = 44
xlCILightOrange = 45
xlCIOrange = 46
xlCIBlueGray = 47
xlCIGray40 = 48
xlCIDarkTeal = 49
xlCISeaGreen = 50
xlCIDarkGreen = 51
xlCIBrown = 53
xlCIIndigo = 55
xlCIGray80 = 56
End Enum

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Dim CIRowColour As Long
Dim CICellColour As Long
Dim CIBorderColour As Long

CIRowColour = xlCILightTurquoise
CICellColour = xlCILightYellow
CIBorderColour = xlCIBlue

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