Forum Discussion

balange1's avatar
balange1
Copper Contributor
Oct 22, 2024

Trigger a cell based on the content of another cell

I am trying to have a create a few conditional formulas or triggers and I am missing the logical argument somewhere. I am trying to get cells in Column J to automatically enter "All Sources" if the s...
Formulas and Functions
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Oct 22, 2024

balange1 

If column J should be blank if column F does not contain "Term", you can use a formula: in J2:

=IF(F2="Term", "All Sources", "")

and fill down.

 

If the user should be able to enter or select a value in column J, you cannot use a formula. You need VBA code. This will work in the desktop version of Excel for Windows/Mac, and it requires that users allow macros.

To do this:

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Range("F2:F" & Rows.Count), Target) Is Nothing Then Exit Sub
    If Target.Value = "Term" Then
        Application.EnableEvents = False
        Target.Offset(0, 4).Value = "All Sources"
        Application.EnableEvents = True
    End If
End Sub

Resources