Clear Contents of cell A when contents of cell B changes - VBA

[spydey]

I have some code that I currently use where if cell A changes, then cell B adjusts accordingly. If cell A is cleared, then cell B is cleared too.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


    If Target.Count = 1 And Target.Column = 7 Then
        If UCase(Target.Value) = "PASS" Or UCase(Target.Value) = "FAIL" Then
            Target.Offset(0, 1) = Date
        Else
            Target.Offset(0, 1).ClearContents
        End If
    End If


End Sub

Works great!

However, I need to use something similar for a different project. So I adjusted the code as needed. But now, when the contents of cell A are cleared, cell B does not clear out. I have tried a few different approaches and I don't seem to get the correct results.

What is wrong with my code?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


    If Target.Count = 1 And Target.Column = 5 Then
        If IsNumeric(Target.Value) = True Then
            Target.Offset(0, -3) = Date
        Else
            Target.Offset(0, -3).ClearContents
        End If
    End If


End Sub

I also tried this, but to no avail:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


    If Target.Count = 1 And Target.Column = 5 Then
        If IsNumeric(Target.Value) = True Then
            Target.Offset(0, -3) = Date
        ElseIf IsEmpty(Target.Value) = True Then
            Target.Offset(0, -3).ClearContents
        End If
    End If


End Sub

Any thoughts? I bet it is something very obvious!! hahahah lol

-Spydey

P.S. I noticed that with my original code, I could paste the PASS or FAIL into the cells, and the dates would change, as needed. However, with pasting the numbers into this new project, the date doesn't change. For this new project, the value in column 5 will always be numeric (always numbers).

 

[spydey]

Ok, so for some reason, it doesn't want to add the date, or clear our the date.

I tried it on a blank, new, fresh workbook, and it works fine. So there is something with my current project workbook that is causing it to not update the correct cell .... I will investigate more and report back.

Thanks for your help so far Fluff!

-Spydey

Ok, I got it to work. I had to clear all formats in my worksheet, then select each column and assign the corresponding format. Then I tried the code and it worked for both adding the date and removing it once the number value is cleared.

Thanks for all your help Fluff and those very insightful and helpful links!

-Spydey

 

[Fluff]

Glad to help & thanks for the feedback

 

[spydey]

So, after using it for a bit, it works for once scenario, but not for the other.

If I just input a single number into the 5th column (column E), then the date appears where it should.

However, if I copy over a set of numbers and paste them (vertically) into column E, then no dates appear at all. The same if I copy a set of data horizontally and paste it starting in column E, the first item containing a number, the rest a variation of different dates, numbers, text, etc. No dates appear at all where they should in column B.

Also, if I have a set of numbers in column E, select all of them, and delete them, the dates remain. If I remove each number individually, then the dates are cleared.

I need to be able to copy and paste large sets of data, both vertically into column e (all numbers), and/or horizontally starting in column E, and have the date auto-populate in column B for all the cells in column E that have been updated with numbers.

Likewise, if I remove numbers from column E vertically, or a data set horizontally starting with column E, once that numeric value is cleared from column E, then the date should clearout in column B, regardless of how many rows in column E vertically and or horizontally (columns) I have selected and cleared. If column E is cleared, then the date in column B should be cleared too.

Hopefully that makes sense.

I am going to poke around in my code for a bit and see if I can come up with something. If anyone has any pointers, suggestions, etc., feel free to share them. I don't necessarily need an answer to my problem (but will not reject one if provided, hehehehe), but more of some instruction, education, coaxing, etc. I enjoy learning and figuring this stuff out.

-Spydey

 

[Fluff]

Part of the problem is that the code will exit if you are changing more than 1 cell.
Try removing this

Target.CountLarge = 1

and see if that helps.

 

[mumps]

Since your target has multiple cells, you will have to loop through each cell. Maybe something like this will work:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
    Dim rng As Range
    Application.EnableEvents = False
    For Each rng In Target
        If Len(rng.Value) = 0 Then
           rng.Offset(0, -3).ClearContents
        ElseIf IsNumeric(rng.Value) = True Then
           rng.Offset(0, -3) = Date
        End If
    Next rng
    Application.EnableEvents = True
End Sub

 

[spydey]

Part of the problem is that the code will exit if you are changing more than 1 cell.
Try removing this

Target.CountLarge = 1

and see if that helps.

So I removed this line, but am getting errors when adding/removing more than 1 item at a time. I am going to play around with it a bit.

-Spydey

 

[spydey]

Since your target has multiple cells, you will have to loop through each cell. Maybe something like this will work:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
    Dim rng As Range
    Application.EnableEvents = False
    For Each rng In Target
        If Len(rng.Value) = 0 Then
           rng.Offset(0, -3).ClearContents
        ElseIf IsNumeric(rng.Value) = True Then
           rng.Offset(0, -3) = Date
        End If
    Next rng
    Application.EnableEvents = True
End Sub

Just looking at the code, it appears that it would work. I am going to give it a try and report back to you what my findings are. Thanks for your help!

-Spydey

 

[spydey]

Just looking at the code, it appears that it would work. I am going to give it a try and report back to you what my findings are. Thanks for your help!

-Spydey

Worked as expected! Thanks for taking the time to look into this and helping me. I very much appreciate it!

-Spydey

 

[mumps]

My pleasure.