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.
Thanks for any help you might provide! Andy
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
This question generated 11 answers. To proceed to the answers, click here.
This thread is current as of November 04, 2014.