Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
942 views
in Technique[技术] by (71.8m points)

vba - How do I remove a fill color when data gets entered in cells from an adjacent drop down list?

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Try:

 If Target = "NO" Then
    Target.Offset(0, 1) = "NULL"
    Target.Offset(0, 1).Interior.ColorIndex = xlColorIndexNone
    Target.Offset(0, 2) = "NULL"
    Target.Offset(0, 2).Interior.ColorIndex = xlColorIndexNone

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share
...