CODEWITHSUNDEEP
vbaexcel
Nick
Nick

Reputation: 47

How do I remove the background color of a cell once a value or text is entered into that cell?

I created a macro for a data validation drop-down list that will populate cells in the adjacent two columns either with a value or with a yellow color fill depending on the selection. Below is a picture of how this looks:

enter image description here

When I enter data in the adjacent two cells after I select "YES" from the drop-down list, the yellow color fill remains in place. Below is a picture of how this looks:

enter image description here

Goal: I would like to have the yellow color fill removed or "unfilled" once any value or text gets entered in its cell.

Is there a way to do this in VBA? I am aware this is doable with conditional formatting, but I wanted to see whether this is doable in VBA.

Below is my code that I have drummed up:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


Application.ScreenUpdating = False

If Target.Count > 1 Then Target.Interior.ColorIndex = xlNone

If Target.Count > 1 Then Exit Sub



Select Case Target

Case "YES"

    If Target = "YES" Then
        Target.Offset(0, 1).Interior.ColorIndex = 6
        Target.Offset(0, 2).Interior.ColorIndex = 6

         If Target.Offset(0, 1).Value = "NULL" Then Target.Offset(0, 1).ClearContents
         If Target.Offset(0, 2).Value = "NULL" Then Target.Offset(0, 2).ClearContents

         If Target.Offset(0, 1).Value = "NULL" Then Target.Offset(0, 1).Interior.Pattern = xlNone

  If Target.Offset(0, 2).Value = "NULL" Then Target.Offset(0, 2).Interior.Pattern = xlNone

            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 Target.Offset(0, 1).Interior.ColorIndex = 6 Then Target.Offset(0, 1).Interior.Pattern = xlNone

  If Target.Offset(0, 2).Interior.ColorIndex = 6 Then Target.Offset(0, 2).Interior.Pattern = xlNone

            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
End Select
End Sub

I would appreciate any help on this matter!

Upvotes: 0

Views: 1755

Answers (3)

perfo
perfo

Reputation: 412

   Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'*** this assumes your yes no is in col A  and you potentially have data in col b and col c ***
'**declare your variables ***
Dim Check_Word As String
Dim Check_Data_ColB As String
Dim Check_Data_ColC As String


'** only run the code if something in col A B or C gets changed *****

If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 3 Then

   '**** set check word to the value in col A ***
   Check_Word = Trim(UCase(Sh.Cells(Target.Row, 1).Text))
   '**** set check_data_colB to the value in col B ***
   Check_Data_ColB = Trim(Sh.Cells(Target.Row, 2).Text)
   '**** set check_data_colC to the value in col C ***
   Check_Data_ColC = Trim(Sh.Cells(Target.Row, 3).Text)

   '*** If the check word is NO or the check word is yes but there is text in col B or C then clear the cells colour ***
   If Check_Word = "NO" Or (Check_Word = "YES" And (Check_Data_ColB <> "" Or Check_Data_ColC <> "")) Then
     '*** all other situations result in the cells getting filled in with Yellow ****
     Sh.Cells(Target.Row, 2).Interior.ColorIndex = 0
     Sh.Cells(Target.Row, 3).Interior.ColorIndex = 0
   Else
     '*** all other situations result in the cells getting filled in with Yellow ****
     Sh.Cells(Target.Row, 2).Interior.Color = vbYellow
     Sh.Cells(Target.Row, 3).Interior.Color = vbYellow
   End If


End If
End Sub

Upvotes: 0

Cyril
Cyril

Reputation: 6829

Thought you had duplicated: How do I remove a fill color when data gets entered in cells from an adjacent drop down list?

Looks like you want conditional formatting, now, as opposed to just turning off the color. You can either turn it on with Excel, or via VBA, similar to:

Sheets("NAME").Cells.FormatConditions.Delete
With Sheets("NAME").Range("B2:C10000")
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(ISBLANK($B2),$A2=""Yes"")"
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        With .Interior
            .ColorIndex = 6
        End With
    End With
End With

This would replace your code completely for adding and removing color.

Upvotes: 1

Pslice
Pslice

Reputation: 54

This sub will change the back fill with conditional formatting, you can just change the range to match what you want. You can also do conditional formatting without any VBA, but I think this is what you want:

Sub FormatForValues()
Dim rngCells As Range
Set rngCells = Range("D9:D16")
    rngCells.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(D9))>0"
rngCells.FormatConditions(rngCells.FormatConditions.Count).SetFirstPriority
    With rngCells.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
   rngCells.FormatConditions(1).StopIfTrue = False
End Sub

Upvotes: 0

Related Questions