Cell Select

[Guest]

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

 

[Guest]

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.

 

[Guest]

Functional workbook with both methods included:
http://www.jlathamsite.com/uploads/for_zephyr.xls

Instructions for adding code to a workbook:
http://www.jlathamsite.com/Teach/Excel_GP_Code.htm
or at Debra Dalgleish's site
http://www.contextures.com/xlvba01.html

 

[Guest]

Dear Mr Latham

Thank you for the download on you're website.

You're assistance is appreciated.

Many thanks

Zephyr

 

[Guest]

I hope it helps some. Sometimes there's just nothing like a working example
to let someone see how it actually works in the real world.