Displaying contents of a cell based on 1st occurance of a colour

[Paul Naylor]

Hi , hoping someone can help

I've got a spreadsheet or orders , order numbers ( column A and dates orders were placed in column B.

Column A is also colour codes to signify the status of the order ( "Yellow" pending, "Green" completed & "Red" cancelled)

Need a formula/VBA which will identify the 1st occurance of a colour , in this case Yellow and then display the contents of the adjacent cell column B i.e. date of the order. ( using excel 97)

Paul

 

[mumps]

This macro assumes the colours were inserted manually and not a result of conditional formatting. It will prompt you to enter the colour to search. The colorindex numbers I used in the code are 3 for red, 6 for yellow and 4 for green. If the macro doesn't work for any colour, you have to change the numbers in the code to the actual colorindex numbers of the colours you used.

Sub FindColour()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, response As String, num As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    response = InputBox("Enter the colour to search: yellow, red or green")
    If response = "" Then Exit Sub
    Select Case response
        Case "yellow"
            num = 6
        Case "red"
            num = 3
        Case "green"
            num = 4
    End Select
    For Each rng In Range("A2:A" & LastRow)
        If rng.Interior.ColorIndex = num Then
            MsgBox rng.Offset(, 1)
            Exit For
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub