Change Date when one of 4 fields = string

here is the file hope it helps.

I found a work around that seems to work any one see any issue with this .

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim LastRow As Long
    Dim WorkRng As Range
    Dim Rng As Range
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    ' Set the intersect ranges to look for changes
    Set WorkRng = Intersect(Application.ActiveSheet.Range("M7:M" & LastRow & ", O7:O" & LastRow & ", Q7:Q" & LastRow & ", S7:S" & LastRow), Target)

    If Not WorkRng Is Nothing Then ' Is the intersects not empty
        Application.EnableEvents = False
        For Each Rng In WorkRng
            If VBA.IsEmpty(Rng.Value) Then ' Check if cell has stuff in it if not use last date
                Range("K" & Target.Row).Formula = "=IFERROR(IF(ISBLANK(INDEX(otadd($K$7:$K$" & LastRow & "),MATCH(B" & Target.Row & ",otadd($B$7:$B$" & LastRow & "),0)))," & Chr(34) & "Not Polled" & Chr(34) & ",INDEX(otadd($K$7:$K$" & LastRow & "),MATCH(B" & Target.Row & ",otadd($B$7:$B$" & LastRow & "),0)))," & Chr(34) & "Not Polled" & Chr(34) & ")"
            Else 'Update date and time
                If Range("L" & Target.Row).Value >= "1" Or Range("N" & Target.Row).Value >= "1" Or Range("P" & Target.Row).Value >= "1" Or Range("R" & Target.Row).Value >= "1" Then
                    Range("K" & Target.Row).Value = Now
                Else ' set date and time for last update
                    Range("K" & Target.Row).Formula = "=IFERROR(IF(ISBLANK(INDEX(otadd($K$7:$K$" & LastRow & "),MATCH(B" & Target.Row & ",otadd($B$7:$B$" & LastRow & "),0)))," & Chr(34) & "Not Polled" & Chr(34) & ",INDEX(otadd($K$7:$K$" & LastRow & "),MATCH(B" & Target.Row & ",otadd($B$7:$B$" & LastRow & "),0)))," & Chr(34) & "Not Polled" & Chr(34) & ")"
                End If
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

I was using a regular formula to do this but i realized that when a update on the page happens it would change all cells with the Now or today function in the formula. while if i use this in VBA they do not update unless that row is updated. this is useful for me as the rolls are sorted by time and needs to be more they just date.

Originally Posted by Richard Buttrey

Is it necessary to process a whole range of cells in M7:M..whatever i.e. the WorkRng range whenever a change is made to a single cell?

yes i think so. Columns M O Q S are for user input. and based on those column K gets update with date. or uses previous sheet date. and you moved M7 down to M lastcell then move to O then Q then S
M could be blank so then O could be the control cell for K. ect..

Isn't this somewhat wasteful.? Surely you only need to operate with the row being changed,

im not sure if there is a way to do it with using less resources, Im more then happy to change but as of now I haven't found a better way.

don't fully understand what you need the OTADD function for nor why regular Excel functions won't work.

The OTADD function was me trying to shorten up formulas. it is for pulling the data from the previous sheet with out me having to know the previous sheets name ahead of time. this workbook is setup to make new sheets with all the required data from the current sheet, with formulas that will look at the previous sheet for data verification and restore data that was changed incorrectly. I was having issues with when the create new sheet was used it would create the sheet exactly as i wanted with the exceptions of not linking to correct previous sheet. the OTADD function fixed that issue. the name was for an entirely different use that worked better for this.

there are some function / code in there from when i was creating a Results Sheet in stead of locking down the current sheet. i changed views on this when i realized this was creating un-needed sheets/resources being used. i also wanted to limit the number of conditional formatting and how many sheets i had to choose from. aka Sheet 1 gets copied to Results 1 then sorted. then Sheet 2 is created from Results 1. rinse and repeat.

in the version i posted i removed all the protection code so that it didnt lock while yall were looking at it.
Current method is Sheet 1 when done is sorted and finalized. finalizing removes all formulas and locks the sheet down. reducing the file size and resources needed. if editing is needed after words the sheet will reacquire all the needed formulas. Using the previous sheet data and pre-finalized data.