I wrote the following code to make it easier to read across rows on a
spreadsheet. I declared the array as a public variable in another
module. It works but it is slow. Is there a slicker way of doing this
that is not so slow and returns the rows to their original color?


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Static OldRange As Range
Dim i As Integer
Dim j As Integer


If Not OldRange Is Nothing Then
For i = 1 To UBound(clrindx, 1)
For j = 1 To 256
Cells(OldRange.Row + i - 1, j).Interior.colorindex = clrindx(i, j)
Next
Next
End If

ReDim clrindx(1 To Selection.Rows.Count, 1 To 256)
For i = 1 To Selection.Rows.Count
For j = 1 To 256
clrindx(i, j) = Cells(ActiveCell.Row + i - 1, j).Interior.colorindex
Next
Next
Target.EntireRow.Interior.colorindex = 15
Target.Interior.colorindex = 6
Set OldRange = Target

End Sub

I like Chip's RowLiner add-in to make it easier to read across rows and/or
columns. It applies formatting to the cell borders.

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


"bobbo" wrote:

> I wrote the following code to make it easier to read across rows on a
> spreadsheet. I declared the array as a public variable in another
> module. It works but it is slow. Is there a slicker way of doing this
> that is not so slow and returns the rows to their original color?
>
>
> Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
> Target As Range)
> Static OldRange As Range
> Dim i As Integer
> Dim j As Integer
>
>
> If Not OldRange Is Nothing Then
> For i = 1 To UBound(clrindx, 1)
> For j = 1 To 256
> Cells(OldRange.Row + i - 1, j).Interior.colorindex = clrindx(i, j)
> Next
> Next
> End If
>
> ReDim clrindx(1 To Selection.Rows.Count, 1 To 256)
> For i = 1 To Selection.Rows.Count
> For j = 1 To 256
> clrindx(i, j) = Cells(ActiveCell.Row + i - 1, j).Interior.colorindex
> Next
> Next
> Target.EntireRow.Interior.colorindex = 15
> Target.Interior.colorindex = 6
> Set OldRange = Target
>
> End Sub
>
>