Hello! Have been reading these forums for weeks while doing some work and the helpfulness is really endless! I have found helpful bits of code that works really really well, but it's very slow! It seems like it needs to execute after EVERY change to the worksheet in order to work.
This code, firstly, protects the worksheet and locks those cells that are color-coded other than green and yellow.
Then, it checks if there is a change to the cell and if there is a change to a cell it recolors the cell as yellow.
It runs very fast in a small worksheet with no data, but once I use the full Worksheet with 5 color-codes and up to 2000 rows it becomes very slow.
Is there a faster solution, like if I limited the working range of the sheet? I really only need to protect BM2:DC2000, not the whole thing.
Thank you for any ideas.
![]()
Sub Worksheet_Change(ByVal Target As Range) 'Do nothing if more than one cell is changed or content deleted If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub 'Protect the sheet ActiveSheet.Protect UserInterfaceOnly:=True 'For green or yellow cells, unlock. For all else, lock. Dim c As Range ActiveSheet.Cells.Locked = True For Each c In ActiveSheet.UsedRange If c.Interior.ColorIndex = 15 Or c.Interior.ColorIndex = 6 or c.Interior.ColorIndex = xlNone Then c.Locked = False End If Next c Set c = Nothing 'Colorize by yellow. Dim isect As Range Set isect = Application.Intersect(Target, Range("BM2:DC2000")) If Not isect Is Nothing Then Target.Interior.ColorIndex = 6 Else: Target.Interior.ColorIndex = xlNone End If End Sub
Bookmarks