Array slowing down event procedure
 

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

Array slowing down event procedure
 

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