VBA worksheet change when copy paste multiple range

[priisha]

Hello everyone,

I'm back here for help again so please bear with me.

I have a vba code that is triggered when there is a change in a range of cells. It works well if the changes are done cells by cells but i get an error in the case if i were to copy and paste for multiple cells in one go.

I hope you can help me adjust it so the code can still work if i were to copy and paste
here is the current VBA code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B20:C35")) Is Nothing Then
    If Target.Value <> OldValue Then
        Target.Interior.Color = vbWhite
        Target.Borders.LineStyle = xlNone
    End If
End If
End Sub

thank you !

 

[offthelip]

You haven't given us all your code: where does Oldvalue get set?? Also what do you want to happen when you paste a range of cells??

 

[Georgiboy]

As OldValue has not been defined i have assumed that you just want to reset the formatting in the pasted range, give the below a try:
Note: If your paste range goes outside of the target area the code will still amend those cells

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range
  
    If Not Intersect(Target, Range("B20:C35")) Is Nothing Then
        For Each rCell In Target.Cells
            With rCell
                .Interior.Color = xlNone
                .Borders.LineStyle = xlNone
            End With
        Next rCell
    End If
End Sub

 

[priisha]

As OldValue has not been defined i have assumed that you just want to reset the formatting in the pasted range, give the below a try:
Note: If your paste range goes outside of the target area the code will still amend those cells

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range
 
    If Not Intersect(Target, Range("B20:C35")) Is Nothing Then
        For Each rCell In Target.Cells
            With rCell
                .Interior.Color = xlNone
                .Borders.LineStyle = xlNone
            End With
        Next rCell
    End If
End Sub

This code worked wonder !!!
In the previous vba code where I had Oldvalue it was not defined in my vba and it just referred to the old value that was in the cell previous to the change. Another question if I had multiple range can I enter it as

Target, range("B20:C35", "C8:C13")?

 

[Georgiboy]

You're welcome, thanks for the feedback.

For your additional question, the syntax would be as below:

Target, Range("B20:C35,C8:C13")

 

[priisha]

You're welcome, thanks for the feedback.

For your additional question, the syntax would be as below:

Target, Range("B20:C35,C8:C13")

ok, i thought the intersect function cant be used for multiple non continugious ranges and that the code should be

Set uRanges = Union(Range("B20:C35"), Range("C8:C13"))       
  
If Not Intersect(Target, uRanges) Is Nothing Then

 

[Georgiboy]

Did you test it?