VBA Code to clear contents of a cell automatically when a trigger value is detected as the lowest value in another column

[JohnGow383]

Hi all. I was hoping I could get some help with some VBA code.
I would like one particular cell (I5) to have contents cleared with one of either of the following two trigger words selected in another column (M4:M53). The trigger words are "NOON in PORT" or "NOON in TRANS".
Also, to make things clear, the trigger words have to be the last value in the mentioned column range. For example, if M25 has "NOON in PORT" selected it will clear I5. The spreadsheet is being copied, renamed with new date and being updated on a daily basis. So if the next day M26 has just "NOON at SEA" I5 will not be cleared and so on. Thanks for any help.

 

[The_Macrotect]

Hi John,

Right click on the sheet you're working on, and select "View Code". Paste this in, and test it out on a copy of your work:

Private Sub Worksheet_Change(ByVal target As Range)

Dim triggercells As Range

Set triggercells = Range("M4:M53")

If Not Application.Intersect(triggercells, Range(target.Address)) Is Nothing Then

For Each Cell In triggercells.Cells
    If Cell.Value = "NOON in PORT" Then
        ActiveSheet.Range("I5").ClearContents
    End If
    If Cell.Value = "NOON in TRANS" Then
        ActiveSheet.Range("I5").ClearContents
    End If
Next

End If

End Sub

 

[JohnGow383]

Hi. Thanks so very much for doing this, it works perfectly. Thank you for taking the time to do this code. Amazing.

Hi John,

Right click on the sheet you're working on, and select "View Code". Paste this in, and test it out on a copy of your work:

Private Sub Worksheet_Change(ByVal target As Range)

Dim triggercells As Range

Set triggercells = Range("M4:M53")

If Not Application.Intersect(triggercells, Range(target.Address)) Is Nothing Then

For Each Cell In triggercells.Cells
    If Cell.Value = "NOON in PORT" Then
        ActiveSheet.Range("I5").ClearContents
    End If
    If Cell.Value = "NOON in TRANS" Then
        ActiveSheet.Range("I5").ClearContents
    End If
Next

End If

End Sub

 

[The_Macrotect]

It was my pleasure, John. Have a great week!

 

[JohnGow383]

Hi. The only niggle with this (and it's not too important), but if there's been a "NOON in PORT" in the column then the cell will clear the following days regardless of what is entered as it detects it from before. Idealy I'd want it to look at the last value in the range M4:M53 but I can live with it. Thanks again.

 

[The_Macrotect]

Hi. The only niggle with this (and it's not too important), but if there's been a "NOON in PORT" in the column then the cell will clear the following days regardless of what is entered as it detects it from before. Idealy I'd want it to look at the last value in the range M4:M53 but I can live with it. Thanks again.

John,

I believe this will get you the desired result. Change the code to this, and give it a whirl:

Private Sub Worksheet_Change(ByVal target As Range)

Dim triggercells As Range, lrow As Integer

Set triggercells = Range("M4:M53")

If Not Application.Intersect(triggercells, Range(target.Address)) Is Nothing Then
    lrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "M").End(xlUp).Row
    If Cells(lrow, 13).Value = "NOON in PORT" Then
        Range("I5").ClearContents
    End If

    If Cells(lrow, 13).Value = "NOON in TRANS" Then
        Range("I5").ClearContents
    End If
End If

End Sub

 

[JohnGow383]

John,

I believe this will get you the desired result. Change the code to this, and give it a whirl:

Private Sub Worksheet_Change(ByVal target As Range)

Dim triggercells As Range, lrow As Integer

Set triggercells = Range("M4:M53")

If Not Application.Intersect(triggercells, Range(target.Address)) Is Nothing Then
    lrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "M").End(xlUp).Row
    If Cells(lrow, 13).Value = "NOON in PORT" Then
        Range("I5").ClearContents
    End If

    If Cells(lrow, 13).Value = "NOON in TRANS" Then
        Range("I5").ClearContents
    End If
End If

End Sub

Wow! That's amazing 'The Macrotect'. Thanks so much, that works perfectly. I'm in awe of your coding knowledge, thanks

 

[The_Macrotect]

I'm glad it works! Thanks so much for your kind words!

 

[JohnGow383]

Hi, sorry to be a pain but I have another problem that I'm trying to solve. It is very similar to the one you provided the solution for except there is one key difference.
Similar to the autotrigger, this time, when I click clear all the data on the righthand side of the sheet (voyage data is cleared), and the daily data is also cleared. The user then inputs the daily figures above. Right now, we are manually copying the counter number in C4 to E7 at the start of each voyage where FAOP is the only value in column range M3:M53. The number in E7 remains there for the entire voyage. Every day the spreadsheet is copied, renamed and opened, they press 'Carry Forward' button which copies yesterdays data into the adjacent cells and clears that data and allows them to enter the new daily data. I then press the 'Noon' button and this fills up the voyage data on the right. So to surmize:

• After pressing clear and populating C4 I'd like this to copy to E7 one time only. This would only occur when only FAOP is present as the last value in M3:M53 and after a number has been entered into C4.
• Similarly I'd like C4 to copy to D22 when SOP has been selected as the last value in M3:M53
• Same for ROP, C4 to D23
• Same for SOP2, C4 to D25 and ROP2, C4 to D26.

I can use your method with some success the problem is it's a once only copy, somethimes they may press Carry Forward first and enter number and then select SOP for example, or they may select SOP first and then press Carry Forrward, in that case the wrong number will have been copied into D22. So I guess the solution would be to copy and paste every time a new number is enetered into C4 when either FAOP, SOP, ROP, SOP2 or ROP2 is the last value in the range of M3:M53. I have uploaded a copy of the spreadsheet to make it clearer.

No worries if this is too much for you but I do thank you in advance for having a look. Thanks John.

Edit: Unable to upload the spreadsheet with exisiting code

 

[The_Macrotect]

John,

I'd be happy to take a look, but it's too hard to follow along without the upload. Can you try uploading again?