Lookup column header based on row cell colour change

sandwichgirl

New Member
Joined
Aug 13, 2007
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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?

1633079865785.png


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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,927
Office Version
  1. 365
Platform
  1. Windows
First, copy and paste the following custom function into a regular module (Visual Basic Editor >> Insert >> Module) . . .

VBA Code:
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...

MatchByColor.PNG


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!
 
Solution

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,927
Office Version
  1. 365
Platform
  1. Windows
You're very welcome, cheers!
 

kenji_midori

New Member
Joined
Dec 14, 2021
Messages
2
Office Version
  1. 2021
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,927
Office Version
  1. 365
Platform
  1. Windows
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 . . .

VBA Code:
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 . . .

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

Hope this helps!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,967
Messages
5,767,370
Members
425,409
Latest member
Whatisanexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top