VBA If this = "Outage" then change that to 7

[PaulF]

Hello all... I have an Excel sheet with a data validated column range: O26:O35 with labor categories of: PreOut Outage, Outage, Post Outage, OnLine Core, Project, Project Special.

When Outage is selected I need the number in same row and column range of L26:L35 to be changed to 7. When I select Outage from the dropdown I get a 7 in Col L then Excel loops over and over on: Range(DaysWksCOL & dROW).Value = Num and Excel blows up :-(

Private Sub Worksheet_Change(ByVal Target As Range)

Dim dROW As Integer
Dim Num As Integer
Dim LabCatCOL As String
Dim DaysWksCOL As String

dROW = 26
LabCatCOL = "O"
DaysWksCOL = "L"
Num = 7
   
Do Until dROW > 35
    If Range(LabCatCOL & dROW) = "Outage" Then
        Range(DaysWksCOL & dROW).Value = Num
    End If
   
    dROW = dROW + 1

Loop

End Sub

 

[NARAYANK991]

Hi ,

Surround the following line of code :

Range(DaysWksCOL & dROW).Value = Num

with the following two lines of code :

Application.EnableEvents = False
Application.EnableEvents = True

as shown below :

Application.EnableEvents = False
Range(DaysWksCOL & dROW).Value = Num
Application.EnableEvents = True

Narayan

 

[PaulF]

Hi ,

Surround the following line of code :

Range(DaysWksCOL & dROW).Value = Num

with the following two lines of code :

Application.EnableEvents = False
Application.EnableEvents = True

as shown below :

Application.EnableEvents = False
Range(DaysWksCOL & dROW).Value = Num
Application.EnableEvents = True

Narayan

Thank you... works like a champ...
Now I just need to go Google to understand why