Excel Highlight Second Entry within a Range of Cells

Question

I am trying to highlight the cell of a second entry (and beyond) within a range of cells. I am tyring to implement this feature in my attendence tracking.

For example, in the image below, I have "Peter", "John" and "Tom". If there are only one person who is absent, I do not need to highlight anything, as shown below.

However, if either "Tom" or "John" is the second person to be entered as "Not in" (absent), I want to highlight the cells, as shown below.

Finally, if the last person left is also absent, I would like to highlight it as well, like below image

The values in Column C is not restricted to these phrases ("Absent","Away","Not in").

From another point of view, I want to highlight the second and the later entries within a range of cells. Is there a way to implement such feature without using VBA or macro? Can it be donne using conditional formating?

Please advise. Thank you so much!

Answer 1

You can use the Worksheet_Change event to add a timestamp to a helper column. In my case, I added it to column D:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myrange As Range
    Set myrange = Range("C3:C9")
    If Not Application.Intersect(myrange, Range(Target.Address)) Is Nothing And Target.Count = 1 Then
            If Target.Value = "" Then
                Target.Offset(0, 1).Value = ""
            Else
                Target.Offset(0, 1).Value = Now()
            End If
    End If
End Sub

Then use conditional formatting on column C using something like this:

=$D3>=SMALL($D$3:$D$9,2)

This will highlight any rows where the the timestamp is greater than the second smallest timestamp (ignoring blanks).

Here's an example of it in action. For illustration purposes, I put the order I typed things in column C rather than "Absent", "Not Here", etc.