# Lookup column header based on row cell colour change

#### sandwichgirl

##### New Member
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

### 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
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...

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

##### New Member
Just the job, thank you

#### Domenic

##### MrExcel MVP
You're very welcome, cheers!

#### kenji_midori

##### New Member

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

#### kenji_midori

##### New Member
Very reactive!! Wau Thank you!!!

#### Domenic

##### MrExcel MVP
You're very welcome.

Cheers!

Replies
5
Views
160
Replies
0
Views
230
Replies
1
Views
94
Replies
1
Views
196
Replies
23
Views
413

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.

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?

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