detect location of a color

[benjamin132]

Hi everyone,
In my beautiful Worksheet, I need to know between which city there is a green bar.
I explained : for the first line I need to know that there is a green bar between "Lyon St Priest Coque A1" and "Delandine", and between "Delandine" and "Lyon St Priest Coque A2"
the second line : I need to know that there is a green bar between "Lyon St Priest Coque A1" and "Dumont", and between "Dumont" and "Lyon St Priest Coque A2".
in Output i need to have for example 4 variable with a Table like ["Lyon St Priest Coque A1","Delandine","Delandine","Lyon St Priest Coque A2"].
Thanks by advance.

 

[portews]

Where do you want the list placed and how, separate cells per to/from or a list all in one cell per row? What is the exact colorindex of the bar, 4?

Try this. I made the list go one cell to the right of the last city name. You may have to change the color index

Sub FindColor()
'https://www.mrexcel.com/board/threads/detect-location-of-a-color.1208693/
With Sheets("Sheet1")
    lastrow = .Range("A" & Rows.Count).End(xlUp).Row
    LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column + 2
End With

For j = 5 To lastrow Step 3
    For i = 5 To LastCol
    Cells(j, i).Select
        If Cells(j, i).Interior.ColorIndex = 4 Then
            If x = 0 Then StartCity = Cells(3, i).MergeArea.Cells(1, 1).Value: x = 1
        Else
            If x = 1 Then
                EndCity = Cells(3, i).MergeArea.Cells(1, 1).Value
                x = 0
                If Not Cells(j, lastrow + 1) = "" Then Cells(j, lastrow + 1) = Cells(j, lastrow + 1) & ", " & Chr(10)
                Cells(j, LastCol + 1) = Cells(j, LastCol + 1) & StartCity & " to " & EndCity
            End If
        End If
    Next i

Next j
End Sub

 

[benjamin132]

Awesome you are a genius. Thanks a lot. Just wanted to store the list in a variable because i need it. But i can do it don't worry. Thanks a lot, have a good day

 

[portews]

I just realized I left in the .Select statement. I put this in for testing purposes to watch the code progress. This is not necessary and removing it may speed up processing.