Lookup column header based on row cell colour change

[sandwichgirl]

Hi

It's a long time since I've had a good excel puzzle and this one is a fun one.

I have a table that is made up of:

Column headers - w/c dates 17/10/2021 24/10/2021 31/10/2021 07/11/2021 14/11/2021
and rows with coloured cells (green and yellow) - green shows items in stock, yellow shows next stock expected - so they flow from green into yellow.

I would like to add a formula to the start of the row that identifies when the stock change date is forecast to happen based on the first cell of yellow

Is this possible?

I've found a VBA option that lets me sum the cells by colour - can I use this to do this too? The functions the VBA enables are =sumcellsbycolor() or =countcellsbycolor(). I don't know VBA myself, and rely on very helpful web pages to supply the code and how to info.

TIA

 

[Domenic]

First, copy and paste the following custom function into a regular module (Visual Basic Editor >> Insert >> Module) . . .

Function MatchByColor(ByVal colorRange As Range, ByVal lookupRange As Range) As Variant

    With lookupRange
        If .Rows.Count > 1 Then
            If .Columns.Count > 1 Then
                MatchByColor = CVErr(xlErrNA)
                Exit Function
            End If
        End If
    End With
   
    Dim matchColor As Long
    matchColor = colorRange.Cells(1, 1).Interior.Color
   
    Dim currentPosition As Long
    currentPosition = 0
   
    Dim currentCell As Range
    For Each currentCell In lookupRange.Cells
        currentPosition = currentPosition + 1
        If currentCell.Interior.Color = matchColor Then
            MatchByColor = currentPosition
            Exit Function
        End If
    Next currentCell
   
    MatchByColor = CVErr(xlErrNA)
   
End Function

Then, it can be used in your worksheet, as follows...

B2, copied down:

=INDEX($C$1:$G$1,MatchByColor($A$2,C2:G2))

Note, you'll need to re-calculate your worksheet using Ctrl+Alt+F9 whenever you change the fill color of a cell.

Hope this helps!

 

[sandwichgirl]

Just the job, thank you

 

[Domenic]

You're very welcome, cheers!

 

[kenji_midori]

Hi everybody

I have the same situation as sandiwichgirl, but would need the last cell of yellow (not the first cell). How would the code change in this case?

Thank you
Kenji

 

[Domenic]

First, I have changed the name of the function to LastMatchByColor so that it reflects the true nature of its function. And then I have modified it as follows . . .

Function LastMatchByColor(ByVal colorRange As Range, ByVal lookupRange As Range) As Variant

    With lookupRange
        If .Rows.Count > 1 Then
            If .Columns.Count > 1 Then
                LastMatchByColor = CVErr(xlErrNA)
                Exit Function
            End If
        End If
    End With
   
    Dim matchColor As Long
    matchColor = colorRange.Cells(1, 1).Interior.Color
   
    Dim currentPosition As Long
    For currentPosition = lookupRange.Cells.Count To 1 Step -1
        If lookupRange(currentPosition).Interior.Color = matchColor Then
            LastMatchByColor = currentPosition
            Exit Function
        End If
    Next currentPosition
   
    LastMatchByColor = CVErr(xlErrNA)
   
End Function

Then your worksheet formula would be as follows . . .

=INDEX($C$1:$G$1,LastMatchByColor($A$2,C2:G2))

Hope this helps!

 

[kenji_midori]

Very reactive!! Wau Thank you!!!

 

[Domenic]

You're very welcome.

Cheers!