How to list dates defined by a data validation process

[Paul_Hossler]

1. This is tied to the WS Change event. I couldn't find an easy way to only use WS formulas

2. I changed the DV and applied Custom Number formats; it made it a little easier to talk to the Sub

3, Not sure I totally understood Objective 2, but let me know

Option Explicit


Sub UpdateChart()
    Dim iNumDays As Long, iTemp As Long, i As Long, o As Long
    Dim dateStart As Date, dateEnd As Date
    Dim rTemps As Range
    Dim rowStart As Long, rowEnd As Long
    
    With ActiveSheet
        If .ChartObjects.Count <> 1 Then Exit Sub
        
        Application.EnableEvents = False


        Set rTemps = .Range("A1").CurrentRegion


        iNumDays = .Range("G34").Value
        iTemp = .Range("G37")
        Range(.Range("I37"), .Range("I37").End(xlDown)).ClearContents
        .Range("I37").Value = "None"
        


        With rTemps
            If iNumDays > .Rows.Count Then iNumDays = .Rows.Count - 1
            rowStart = .Cells(.Rows.Count - iNumDays + 1, 1).Row
            rowEnd = .Cells(.Rows.Count, 1).Row
            dateStart = .Cells(rowStart, 1).Value
            dateEnd = .Cells(rowEnd, 1).Value
        End With
            
        o = 37
            
        For i = rowStart To rowEnd - 2
            If Abs(.Cells(i, 2).Value - iTemp) >= 3 And Abs(.Cells(i + 1, 2).Value - iTemp) >= 3 And Abs(.Cells(i + 2, 2).Value - iTemp) >= 3 Then
                .Cells(o, 9).Value = .Cells(i, 1).Value
                o = o + 1
            End If
        Next i
        
        With .ChartObjects(1).Chart
            .Axes(xlCategory).MinimumScale = CDbl(dateStart)
            .Axes(xlCategory).MaximumScale = CLng(dateEnd)


            .ChartTitle.Caption = "Temperature " & dateStart & " - " & dateEnd & Format(iNumDays, " (## days)")
        End With


        Application.EnableEvents = True


    End With


End Sub