Any number in a column is greater than last one entered

[skyport]

Hoping someone can help me with this one. I've tried the COUNTIF and the IF methods I could think of and I can't figure how to make a simple formula I can put into the cells of a column that will do the following:


Consider that Column E needs to contain a list of dates.

1. I need a formula where each time I put a new date in the column E, it would check all the previous dates entered in column E and then....

2. flag or show a warning in Column H in the form of a RED letter "A" whenever there is a previous date already in column E that is greater than the most recent entry date made in column E.

3. As long as no previous dates entered in Column E were greater than the most recent date entered in Column E, then column H would show nothing.

The main idea is that I need to see the warning in column H right away each time a date is added to column E only when there is an existing date in column E that is greater than the most recent one just entered.

Please help me with this if someone has the answer.

Thanks

 

[alansidman]

Put this code in the worksheet change event for particular sheet in your worksbook

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr As Long
    lr = Range("E" & Rows.Count).End(xlUp).Row
    If Target.Column = 5 Then
       If Target(lr, 5) < Application.WorksheetFunction.Max(Range("E1:E" & lr - 1)) Then
        thisrow = Target.Row
        Range("H" & thisrow) = "A"
        Range("H" & thisrow).Interior.ColorIndex = 3
        End If
    End If
End Sub

Accessing the Private Module of the Worksheet Object

To gain access to the private module of the Worksheet Object.

1) While in Excel proper, right click on the Worksheet name tab and choose View Code.

2) While in the VBE double click the Module called Sheet* (Sheet*), seen in the Project Explorer (View>Project Explorer (Ctrl+R)) under Microsoft Excel Objects.

Once here, you can select "Worksheet" from the Object drop down list, located in the top left of the module pane. After you have selected "Worksheet" Excel will default to;

Private Sub Worksheet_Change(ByVal Target As Range)

 

[skyport]

Alan, thanks so much for helping me with this. I followed all instructions however, the program does not show up in the run window. I put other program in a module to see if the problem existed their too but that program loaded and was accessible. Are we missing some minor detail?

 

[alansidman]

It should not appear in the run window as it is not a macro that you run on demand. It is a worksheet change event that sits in the background and when you make changes or enter data in column E, it will evaluate and return the results in column H as requested.

 

[skyport]

Hi again Alan, Glad you clarified. The only two problems that seem to occur are as follows: Although I love the idea of the red shaded cell as the flag coming up in column H, it appears to be returning all results with a red warning regardless of whether the dates are in order or there is one previous that is greater. IE; every cell in column H is coming up red as a date is entered in column E. I tried it with both dates and just single numbers to make sure and I formatted the cells appropriately each time. One other thing is that once column H is marked with a red flag warning, if i go and eliminate the dates in column E to start over, I cannot eliminate the red cells in column H to start fresh. However, I can see we are certainly heading in the right direction.

One time when I was trying to clear the red cells in column H, the program requested a debug in the following line, which of course I don't know what that would mean:

If Target(lr, 5) < Application.WorksheetFunction.Max(Range("E1:E" & lr - 1)) Then thisr ow = Target.Row

Thanks for sticking with me on this. I really appreciate your help.

 

[alansidman]

Try using this code instead:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr As Long
    lr = Range("E" & Rows.Count).End(xlUp).Row
        thisrow = Target.Row
        If Target.Value < Application.WorksheetFunction.Max(Range("E1:E" & lr - 1)) Then
        Range("H" & thisrow) = "A"
        Range("H" & thisrow).Interior.ColorIndex = 3
        End If


End Sub

 

[skyport]

Really like the added touch with the A in the red cell. Still delivers a red cell each time regardless of the date. If it makes it easier, it could work if just the previous date in column E that is greater to last date entered would get the red flag showing it to be the culprit date causing the alarm. Also, still can't get rid of red cells when starting over. have to delete the column