Color formatting speed - Driving me loopy

[Kidd1313]

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

 

[AlphaFrog]

Try this in addition to the previous Worksheet_Change procedure.

This Worksheet_SelectionChange should replace any previous Worksheet_SelectionChange code.

This code will call the Worksheet_Change procedure if the previously selected cell was in column B regardless if you made any change in column B. It still should be fast.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_SelectionChange([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Static[/COLOR] rngPreviousB [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] rngPreviousB [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] Worksheet_Change rngPreviousB
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Range("B:B"), Target) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]Set[/COLOR] rngPreviousB = Intersect(Range("B:B"), Target)
    [COLOR=darkblue]Else[/COLOR]
        [COLOR=darkblue]Set[/COLOR] rngPreviousB = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[Kidd1313]

Try this in addition to the previous Worksheet_Change procedure.

This Worksheet_SelectionChange should replace any previous Worksheet_SelectionChange code.

This code will call the Worksheet_Change procedure if the previously selected cell was in column B regardless if you made any change in column B. It still should be fast.

This took care of my issue! I am left with a few more formatting issues, but this gives me a great jumping off spot to learn from on my own! Thanks again for the help - it was an incredible push in the right direction!

 

[AlphaFrog]

This took care of my issue! I am left with a few more formatting issues, but this gives me a great jumping off spot to learn from on my own! Thanks again for the help - it was an incredible push in the right direction!

You're welcome. Thanks for the feedback.

In the light of day, the Worksheet_SelectionChange procedure could be shortened a bit to this. It does the same as before.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_SelectionChange([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Static[/COLOR] rngPreviousB [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] rngPreviousB [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] Worksheet_Change rngPreviousB
    [COLOR=darkblue]Set[/COLOR] rngPreviousB = Intersect(Range("B:B"), Target)
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]