Excel: Color formatting speed - Driving me loopy


Hi Folks, Thanks in advance for all the help you provided on the forum in getting me this far (from searching other posts). Been a long time away from VB, and just starting back. Question: I am using Excel 2010, and I'd like to format color of cells on my spreadsheet if they are TRUE. I currently use conditional formatting, but it limits the users ability to modify colors to their own taste, and takes me forever to update when I make a change. Each row can have a unique color, and the color is determined by what is found on that specific row in column 2. I have come up with the attached code, and it works, but is super slow. Looking for a more efficient way to handle this style of operation. I loop through each row, find the color in column two and change color of all TRUE cells in that specific row to that color, and non-TRUE cells get changed to white. I realize I could also use a command button to just update the colors all at once, but would prefer to have it update on SelectionChange.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim y As Long
Dim x As Long
For y = 6 To 129
    For x = 7 To 215
        If Sheet1.Cells(y, x).Value = True Then
            Sheet1.Cells(y, x).Interior.Color = Sheet1.Cells(y, 2).Interior.Color
            Sheet1.Cells(y, x).Borders.LineStyle = xlSolid
        Else
            Sheet1.Cells(y, x).Interior.Color = vbWhite
            Sheet1.Cells(y, x).Borders.LineStyle = xlNone
        End If
    Next x
Next y
End Sub
Thanks for any help you might provide! Andy


This question generated 11 answers. To proceed to the answers, click here.

This thread is current as of November 04, 2014.


For more resources for Microsoft Excel