ExcelBanter - Moving highlight

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Moving highlight (https://www.excelbanter.com/excel-discussion-misc-queries/199960-moving-highlight.html)

Moving highlight

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

Moving highlight

Maybe select B2 and WindowFreeze 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

On Sat, 23 Aug 2008 12:04:01 -0700, Michael
wrote:

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

Moving highlight

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.

--
__________________________________
HTH

Bob

"Michael" wrote in message
...
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

Moving highlight

On Aug 23, 12:16*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Maybe select B2 and WindowFreeze 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

On Sat, 23 Aug 2008 12:04:01 -0700, Michael

wrote:
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- Hide quoted text -

- Show quoted text -

Michael,

You can find more information on "banding" he
http://groups.google.com/group/micro...sc&q=b anding

Moving highlight

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" wrote:

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.

--
__________________________________
HTH

Bob

"Michael" wrote in message
...
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

Moving highlight

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

--
__________________________________
HTH

Bob

"Dan the Man" wrote in message
...
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" wrote:

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.

--
__________________________________
HTH

Bob

"Michael" wrote in message
...
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