Default Values

[network_engineer]

Hi all,

Is there any way to fill in default values in excel?

E.g. (example here) In column A, I have a list "High", "Nominal", "Low" as values, if "Low" is selected, I want the cell in column B to automatically have "No" as the value.

Kind regards,
Ben

 

[Comfy]

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target.Value = "Low" Then Target.Offset(0, 1).Value = "No"
End If
    
End Sub

 

[network_engineer]

Thanks a lot!

It worked; one more question: To that code: How would I lock that particular cell so that if "Low" is selected, then the value in the other cell cannot be changed?

Kind regards.

 

[Comfy]

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target.Value = "Low" Then Target.Offset(0, 1).Value = "No"
End If
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    If Target.Offset(0, -1).Value = "Low" Then Target.Value = "No"
End If
End Sub

I'm not sure of how to lock individual cells but this might work.

It will allow the user to type in the cell but will immediately change the value back to No.

/Comfy

 

[network_engineer]

Correct my code please?

Thanks!

Could somebody please check and correct my code for me please? As it stands right now, when I change the values in Column E, Excel hangs.

Private Sub Worksheet_Change(ByVal Target As Range)
' #Comment: This sets the value in column Invite (F) to No, if the Priority is Low
If Not Intersect(Target, Range("E:E")) Is Nothing Then
If Target.Value = "Low" Then Target.Offset(0, 1).Value = "No"
End If
If Not Intersect(Target, Range("F:F")) Is Nothing Then
If Target.Offset(0, -1).Value = "Low" Then Target.Value = "No"
End If

' #Comment: This sets the value in column Status (G) to No, if the Invite is No

If Not Intersect(Target, Range("F:F")) Is Nothing Then
If Target.Value = "No" Then Target.Offset(0, 1).Value = "No"
End If
If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.Offset(0, -1).Value = "No" Then Target.Value = "No"
End If

End Sub

 

[Comfy]

The code I gave you Loops itself.

Will update it to stop this.

 

[Comfy]

Sorry About that:

Private Sub Worksheet_Change(ByVal Target As Range)
' #Comment: This sets the value in column Invite (F) to No, if the Priority is Low
Select Case Target.Column
    Case 5
        If Target.Value = "Low" And Target.Offset(0, 1).Value <> "No" Then Target.Offset(0, 1).Value = "No"
    Case 6
        If Target.Offset(0, -1).Value = "Low" And Target.Value <> "No" Then Target.Value = "No"
        If Target.Value = "No" Then Target.Offset(0, 1).Value = "No"
    Case 7
        If Target.Offset(0, -1).Value = "No" And Target.Value <> "No" Then Target.Value = "No"
    Case Else
        'Do Nothing
End Select

End Sub

This shouldn't hang now due to it calling itself over and over...