If target cell is changed, clear another cell ONLY if contains specific content....please help

[iboth]

Hello,

The code below is supposed/meant to do the following:

if the content of a cell in column B is equal or changes to any value that is part of a list in range B118:B124, the cell in column D (and corresponding/same row) will autopopulate to "Hol". That is accomplished by the first block of code, which works great if run by itself (if I delete the second block).

The second block is meant to clear cells in column D only if the cell contains specific content ("Hol") when the cell in corresponding row of column B is changed to anything....other than any values that are part of the list B118:B124.

What am I missing in the code below? any help would be greatly appreciated. Thanks!

For Each cell In Target

If Not Intersect(cell, Range("B:B")) Is Nothing Then​

If Application.WorksheetFunction.CountIf(Range("B118:B124"), cell) Then​

Range("D" & cell.Row) = "Hol"​

End If​

End If​

Next


If Target.Column = 2 And (Target.Row >= 2 And Target.Row <= 100) Then

If Target.Offset(0, 2).Value = "Hol" Then​

Target.Offset(0, 2).ClearContents​

End If​

End If


End Sub

 

[Fluff]

How about

For Each Cell In Target
   If Not Intersect(Cell, Range("B:B")) Is Nothing Then
      If Application.WorksheetFunction.CountIf(Range("B118:B124"), Cell) Then
         Range("D" & Cell.row) = "Hol"
      Else
         Range("D" & Cell.row).ClearContents
      End If
   End If
Next
End Sub

 

[iboth]

Hi Fluff,

Haven't tried it yet, but this code looks like it would clear cells in column D that contain anything, not just "Hol", when changing B to anything other than the values in the list B118:B124. I would need the code to do the following: when column B contains or is changed to any value within a particular list, column D will autopopulate "Hol". If column B contains or is changed to anything other than values within the list, ONLY clear D if it contains "Hol", otherwise leave it unchanged (that is, if column D was clear before the change, it should remain clear. Likewise, if it contained any value other than "Hol", it should remain unchanged). Hope this helps understanding my goal. Thanks.

 

[iboth]

JoeMo,

I tried your suggestion, but it will still give me a runtime error 13 type mismatch. The specific line highlighted by debugger is

If Target.Offset(0,2).value="Hol" And IsError (Application.Countif(Range("B118:124"),Target)) Then

Any ideas what might cause the error?

Thanks

 

[JoeMo]

Assuming your code is a worksheet_change event try:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Target
If Not Intersect(cell, Range("B:B")) Is Nothing Then
    If Application.WorksheetFunction.CountIf(Range("B118:B124"), cell) Then
    Range("D" & cell.Row) = "Hol"
    End If
ElseIf Cells(cell.Row, "D") = "Hol" Then Cells(cell.Row, "D").ClearContents
End If
Next

End Sub

 

[iboth]

This is my complete code. It includes your version of the 2nd block (well, third in this case).


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And (Target.Row >= 2 And Target.Row <= 100) Then
Target.Offset(0, 8).ClearContents
End If


For Each cell In Target
If Not Intersect(cell, Range("B:B")) Is Nothing Then
If Application.WorksheetFunction.CountIf(Range("B118:B124"), cell) Then
Range("D" & cell.Row) = "Hol"
End If
End If
Next


If Target.Column = 2 And (Target.Row >= 2 And Target.Row <= 100) Then
If Target.Offset(0, 2).Value = "Hol" And IsError(Application.CountIf(Range("B118:B124"), Target)) Then
Target.Offset(0, 2).ClearContents
End If
End If


End Sub

 

[iboth]

ok, for some reason the indents got messed up. I'm pretty sure that every line is aligned/indented correctly though. It's about the content.

 

[JoeMo]

See post #5 which was posted while you were posting #6 .

 

[iboth]

Joe,

I tried it and no luck. Looks like the ElseIf part is not dependent on a change in B. It seems to cancel to code above (within same block).

First, If i input a value from the specified list into B, D is supposed to return "Hol". However, that seems to be canceled by the ElseIf line.
So, if B contains or is changed to value from specified list, D should autopopulate/show "Hol". If B is changed to any other value (not in list), D should clear only if it contains "Hol" - if not, it should be left unchanged.

Currently, if B contains value from list, D does not autopopulate "Hol", because it is cleared by ElseIF line

Hope this helps in terms of what I am trying to accomplish.

thanks

 

[JoeMo]

My apology, I put the elseIf in the wrong place. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Target
    If Not Intersect(cell, Range("B:B")) Is Nothing Then
        If Application.WorksheetFunction.CountIf(Range("B118:B124"), cell) Then
            Range("D" & cell.Row) = "Hol"
        ElseIf Cells(cell.Row, "D") = "Hol" Then Cells(cell.Row, "D").ClearContents
        End If
    End If
Next cell
End Sub