A macro like this could be used to do it:

Sub IDbyColor()
Dim ContentsOfAll As String
Dim WhatColorCode As Integer
Dim anyCell As Object

WhatColorCode = 41 ' light blue
For Each anyCell In Range("A1:W1")
If anyCell.Interior.ColorIndex = WhatColorCode Then
ContentsOfAll = ContentsOfAll & anyCell.Text & ","
End If
Next
If Right(ContentsOfAll, 1) = "," Then
ContentsOfAll = Left(ContentsOfAll, Len(ContentsOfAll) - 1)
End If
Range("F7") = ContentsOfAll

End Sub

You'd need to know the specific code for the fill color/shade of blue
chosen. That could be determined by recording a macro to set a cell's
shading to the color and examining the created macro to see what value was
used.

The UDF (user defined function) version of it:
Public Function ListColoredCellContents()
Dim ContentsOfAll As String
Dim WhatColorCode As Integer
Dim anyCell As Object

Application.Volatile
WhatColorCode = 41 ' light blue
For Each anyCell In Range("A1:W1")
If anyCell.Interior.ColorIndex = WhatColorCode Then
ContentsOfAll = ContentsOfAll & anyCell.Text & ","
End If
Next
If Right(ContentsOfAll, 1) = "," Then
ContentsOfAll = Left(ContentsOfAll, Len(ContentsOfAll) - 1)
End If
ListColoredCellContents = ContentsOfAll
End Function

You can use that in F7 like:
=ListColoredCellContents()

Since it will not be updated just by changing cell shading, you'd need to use
[Ctrl]+[Alt]+[F9] to insure that the information displayed in F7 (or any
place else you'd used the UDF) is current.


"zephyr" wrote:

A row cells A1 to W1 contains text in a selection of them. If various cells
have the background color highlighted blue, is it possible for the text in
the coloured cells to be put in a single cell F7 and seperated by a comma.

Many thanks