@DataDes
What you want to do is not recommended practice.
The reason why is that changing the color of a cell manually does not cause Excel to recalculate. And using Conditional Formatting to change the color of a cell does not play well with user-defined functions that are testing cell color.
If you are willing to accept those limitations, I wrote a function that will return the text from the first "gray" cell above the tested cell. You call it with a worksheet formula like:
=LastGrayCell(B2,B$1)
In the above formula, B2 is the cell being tested for color, while B1 is a cell that has the (gray) color you are looking for. If you put the formula in cell C2 and copy it down, it will return the text in the preceding cells in column B that match the desired color. When the column B cell has that color, an empty string (looks like a blank) is returned.
Note that the formula won't update until the user changes the value of any cell in the workbook. Also, the formula won't respond to colors set by conditional formatting at all. Finally, the formula won't color the cell in column C--but you could use Conditional Formatting for that purpose using the formula criteria <>""
Function LastGrayCell(TestCell As Range, RefColorCell As Range) As Variant
Dim cel As Range, rg As Range
Dim i As Long, n As Long, RefColor As Long
Application.Volatile
LastGrayCell = ""
RefColor = RefColorCell.Interior.Color
If TestCell.Interior.Color <> RefColor Then
Set rg = Range(TestCell, TestCell.EntireColumn.Cells(1))
n = rg.Cells.Count
If n > 1 Then
For i = n - 1 To 1 Step -1
If rg.Cells(i).Interior.Color = RefColor Then
LastGrayCell = rg.Cells(i).Value
Exit For
End If
Next
End If
End If
End Function
