I am trying to create either a macro or formula that I can put in column C that will say if cell in corresponding row in column B has no color then fill the contents in column C with the last cell in column B that has color grey. See before/after images.
Note, the metrics are not actually labeled that way so there is no wildcard option that would work to confirm if it is a metric title versus a name.
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:
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