Find latest date of colored cells from a range of colored and no fill cells

[Timaxrus]

Hello, guys!
I need your help on one project.
[TABLE="width: 500"]
<tbody>[TR]
[TD]V1[/TD]
[TD]V2[/TD]
[TD]V3[/TD]
[TD]V4[/TD]
[TD]V5[/TD]
[TD]V6[/TD]
[/TR]
[TR]
[TD]12-Apr-2018[/TD]
[TD]13-Apr-2018[/TD]
[TD]14-Apr-2018[/TD]
[TD]15-Apr-2018[/TD]
[TD]16-Apr-2018[/TD]
[TD]17-Apr-2018[/TD]
[/TR]
</tbody>[/TABLE]

Let's assume V1, V2, V3, V4 cells are color filled with green, others are not. And I need to find the latest date from that colored range (it should pick from colored cells, because late the other cells are also colored) and return it with the value above it like "V4 13-Apr-2018".
I need that info in a separate cell.
Thank you in advance.

 

[Timaxrus]

Hi guys!
I thought it would not be difficult for people here. Still I am waiting for your help for my above issue. please help me

 

[PCL]

Can you attach a short Excel ,sample to see how your data is.
is it "V4 13-Apr-2018" or "V4 15-Apr-2018"
What is the color the fill color code: Which Green?

 

[PCL]

What is the range to cover, only V1 to V6

 

[Timaxrus]

Sorry guys... the modification of my above question...
Lets assume
1. A1=Visit 1, B1=Visit 2, C1=Visit 3, D1=Visit 4, E1=Visit 5.
2. A2=12.04.2018, B2=13.04.2018, C2=14.04.2018, D2=15.04.2018, E2=16.04.2018
From row 2 dates are color filled till "15.04.2018" with green (36). And I would like a function in a cell that returns the latest date based on color fill and visit info above it.
In my case here it would be "Visit 4 15.04.2018". Thanks for your time!!!

 

[PCL]

Is the color fill prepared with a Conditional Format.
If yes, what is the condition used
Can you give more detail about the 36, is it a colorIndex ??

 

[PCL]

Here a possibility, the function is just checking if there is a fill color change, no reference to the green (36).
If there is no color found the displays is "No End Color Date"
It does not work with Conditional Format
Somewhere put
=EndColor(A2:E2)

Function EndColor(WkRg As Range) As String
Dim I  As Integer
    EndColor = "No End Color Date"
    With WkRg
        For I = 2 To WkRg.Count + 1
            If (.Cells(1, I - 1).Interior.ColorIndex <> .Cells(1, I).Interior.ColorIndex) Then _
               EndColor = .Cells(1, I - 1).Offset(-1, 0) & " - " & .Cells(1, I - 1)
        Next I
    End With
End Function

"

 

[Timaxrus]

No, that is not conditional formatting. That is updated manually and I have another file that gets autoupdated based on the first file

 

[Timaxrus]

Thanks, bro