Need help referencing highlighted cells in a dataset

[RockyV8907]

1994	1995	1996	1997	1998	1999	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014
					6	9	6	9	17	3	1	1	2	1			1

<tbody>
</tbody>

=INDIRECT(ADDRESS(1,MATCH(1000,B2:V2)+1))

I have a dataset with over a 1000 rows. I just pulled one row in my dataset as an example. My formula above finds the last cell with a value in the 2 row, finds the year associated with it in the first row, and pulls the year from the first row. What I need help with is trying find the last value highlighted red and pulling the year associated with it in the first row. I can't seem to figure out a solution and would very much appreciate any input or advice.

Thank you SO much for your assistance!

 

[mumps]

Hi Rocky. Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim x As Long
    Dim c As Range
    Dim lCol As Long
    For x = 2 To ActiveSheet.UsedRange.Rows.Count
        lCol = Range("IV" & x).End(xlToLeft).Column
        For Each c In Range(Cells(x, 1), Cells(x, lCol))
            If c.Font.ColorIndex = 3 And c.Offset(0, 1).Font.ColorIndex <> 3 Then
                MsgBox ("The year associated with row " & c.Row & " is " & Cells(1, c.Column))
            End If
        Next c
    Next x
    Application.ScreenUpdating = False
End Sub