if cell is red then display a value from another cell on the same column.

[StF]

Hello,

I have a time report where the weekends and legal holidays are autofilled in red if an employee is registered as working.
Due to new legislation I need to keep a record of those days.
I would like to auto check if every row(eg. F17:AJ17) has red cells and then paste a date from a cell that is on row 4, same column as the red one.
The final result should look like cell P7.
I already lost several hours and on this and simply can't get it even near right. Thanks a lot.

 

[JB2020]

You could use a UDF such as this (assuming this is the correct shade of red):

Function ContainsRedCells(Reference As Range) As Boolean

Dim cell As Range

    For Each cell In Reference.Cells
        If cell.Interior.Color = 255 Then
            ContainsRedCells = True
            Exit Function
        End If
    Next cell

End Function