Thread: Using Worksheet_Change and User Selection

Hello, I search quite a while with google and also through this forum, but I didn't find an answer to my problem.

I have a excel sheet with several status in coloumn C (open, closed, etc.).
And I have the following macro:

Code:

Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Object

For Each Cell In Range("C3:C250")

If Cell = "closed" Then

' Make the background color of the cell the 3rd color on the
' current palette.
Cell.EntireRow.Interior.Pattern = xlPatternLightUp
Else
Cell.EntireRow.Interior.Pattern = xlPatternNone
End If
Next Cell
End Sub

The code works perfectly and always changes the pattern color when I choose closed.
BUT: When I try to copy and past a cell to several other cells, everytime I perform a CTRL + V the selected cell is unselected and I have to select it again.
Is there a trick or something that the selection is kept after the "Worksheet_Change" ?

Thanks for any answer!

The easiest workaround would be to select all your target cells first and then paste to all of them at once.
If that's not an option, you would have to assign the copied cell contents to a DataObject at the start of the Change event, do your formatting, then put the contents of the DataObject back into the clipboard. Let me know if you want some code for that.

Yes, you could use conditional formatting to do it (I assumed you had a reason for using code instead?)

Then you will need to adapt the Change event. At the very least I would think you would want to check what has been changed - currently your code reformats everything for any change on the worksheet

Sure. This version will check if the changed cell(s) is/are in the range C3:C250:

Code:

Sub Worksheet_Change(ByVal Target As Range)
   Dim rngCell As Range
   ' check if anything in C3:C250 is changed
   If Not Intersect(Target, Me.Range("C3:C250")) Is Nothing Then
      For Each rngCell In Intersect(Target, Me.Range("C3:C250"))
         ' check if the cell value is "closed" (not case-sensitive)
         If StrComp(rngCell.Value, "closed", vbTextCompare) = 0 Then
         
         ' Make the background color of the cell the 3rd color on the
         ' current palette.
            rngCell.EntireRow.Interior.Pattern = xlPatternLightUp
         Else
            rngCell.EntireRow.Interior.Pattern = xlPatternNone
         End If
      Next rngCell
   End If
End Sub