I created a macro that will populate multiple fields in a spreadsheet based on a drop-down selection, for example:
In column L, I have a drop down list of two items, "YES" and "NO". When an item is selected, the adjacent two cells will populate with predetermined data, for example:
Selecting "YES" will fill the two adjacent cells with yellow
Selecting "NO" will populate the two adjacent cells with the word, "NULL"
Here's where I am stuck..
When someone enters data in the adjacent two cells of "YES", I need the yellow fill to go away.
Is there a way to remove the yellow fill when someone enters data into such cells?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False
Select Case Target
Case "YES"
If Target = "YES" Then
Target.Offset(0, 1).Interior.ColorIndex = 6
Target.Offset(0, 2).Interior.ColorIndex = 6
If Not Target.Cells.Count = 1 Then
Exit Sub
If Intersect(Target, Columns(2)) Is Nothing Then
Exit Sub
End If
End If
End If
Case Else
If Target = "NO" Then
Target.Offset(0, 1) = "NULL"
Target.Offset(0, 2) = "NULL"
If Not Target.Cells.Count = 1 Then
Exit Sub
If Intersect(Target, Columns(2)) Is Nothing Then
Exit Sub
If Intersect(Target, Columns(2)) Is Nothing Then
Exit Sub
End If
End If
End If
End If
End Select
End Sub
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…