Match a colored cell with the cell next to it

[Slumbery]

Hey there, I am trying to find a formula to match a colored cell with value of the cell next to it and display that value in a separate cell (copy). Here is an example:

A	B	C
1	1/14/2018	6
4	1/15/2018
6	1/18/2018
7	1/19/2018

<tbody>
</tbody>

So if we have something like this (red texts are the the colored cells). Now, whats happening here is the dates are colored as days progress. Now in cell C, it should display whatever the last colored cell with the corresponding number next to it in A. So for today, it display 6 in C1, as days progress, it should display 7 in C1 as B4 becomes highlighted in red.

Here is how it would look like with the formula.

A	B	C
1	1/14/2018	7
4	1/15/2018
6	1/18/2018
7	1/19/2018

<tbody>
</tbody>

 

[Slumbery]

Bump, still need help.

 

[Rick Rothstein]

How are your cells being colored... manually or by means of Conditional Formatting? If Conditional Formatting, what is the condition that makes the cell get colored?

 

[Slumbery]

How are your cells being colored... manually or by means of Conditional Formatting? If Conditional Formatting, what is the condition that makes the cell get colored?

It is Conditional Formatting and the condition that makes it get colored is any cell that is less than today's date "=<Today()"

 

[Rick Rothstein]

It is Conditional Formatting and the condition that makes it get colored is any cell that is less than today's date "=<Today()"

Assuming Column B is always in ascending order, see if this formula does what you want (change the ranges A1:A100 and B1:B100 to your actual ranges of data)...

=INDEX(A1:A100,COUNTIF(B1:B100,"<"&TODAY()))