ElseIF question

[Jammydan]

Hi, I have the following code which works fine...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim SelectDG As Range
Dim UserDG As Range
Set SelectDG = Range("F10:F100")
Set UserDG = Range("O2")
Application.ScreenUpdating = False
'**** PLEASE SELECT RANGES ****
    For Each Cell In SelectDG
        If Cell.Value = "Please Select" Then
        Cell.Interior.ColorIndex = 36
        ElseIf Cell.Value = "Yes" Then
            With Cell
            .Interior.ColorIndex = 43
            End With
        ElseIf Cell.Value = "No" Then
            Cell.Interior.ColorIndex = 44
        ElseIf Cell.Value = "N/A" Then
            Cell.Interior.ColorIndex = 2
        End If
    Next
End Sub

However as soon as I add

.Offset(0, 1) = UserDG

as below it seems to loop for about 2 minutes.

ElseIf Cell.Value = "Yes" Then
            With Cell
            .Interior.ColorIndex = 43
            .Offset(0, 1) = UserDG
          End With

Can anybody help please?

 

[JackDanIce]

Try this instead:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Long
Application.ScreenUpdating = False

For i = 10 To 100
    Select Case UCase(Range("F" & i))
        Case "PLEASE SELECT"
            Range("F" & i).Interior.ColorIndex = 36
        Case "YES"
            With Range("F" & i)
                .Interior.ColorIndex = 43
                .Offset(0, 1) = Range("O2")
            End With
        Case "NO"
            Range("F" & i).Interior.ColorIndex = 44
        Case "N/A"
            Range("F" & i).Interior.ColorIndex = 2
        End Select
Next i
Application.ScreenUpdating = True
End Sub

 

[Jammydan]

Thanks for replying, however I get the ame problem, the screen flickers for about 20 seconds. Any ideas what maybe causing this?

 

[Ruddles]

Yes, it's because you're triggering a worksheet change inside the procedure which detects worksheet changes, and your procedure is repeatedly triggering itself.

Change this:-

.Offset(0, 1) = UserDG

to this:-

application.enableevents=false
.Offset(0, 1) = UserDG
application.enableevents=true

This will disable the worksheet change event from triggering as a result of that one statement.

 

[Jammydan]

Thank you, did figure this out earlier, another lesson learned!