Forum Discussion

leenash9702's avatar
leenash9702
Copper Contributor
Nov 01, 2022

If condition met, set value in cell, otherwise let user insert the value

Hi. 

 

I have a task checklist which has 2 columns, E5:E60 is the percentage of completion, and F5:F60 is the status (Done, In progress...etc.). When the user selects Done from drop down menu in any cell in column F, I want the value in the corresponding cell in Column E (percentage) to be automatically set to 100%, otherwise, the user inserts the percentage the task is currently at.

 

How do I go about achieving this? VBA is okay.

 

Please note I'm working in Arabic, so my sheet reads from right to left. 

excel
Formulas and Functions
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 01, 2022

    leenash9702 

    Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngBereich As Range

Application.EnableEvents = False

Set rngBereich = Range("F5:F60")

If Not Application.Intersect(Target, rngBereich) Is Nothing Then

If Target.Value = "Done" Then

Target.Offset(0, -1).Value = 1
Target.Offset(0, -1).NumberFormat = "0%"

Else

End If
End If

done:
Application.EnableEvents = True
Exit Sub

End Sub

    You can try these lines of code for a case sensitive match.

Resources