Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Select
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Select
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Select
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 "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Select
Dear Mr Latham
Thank you for the download on you're website. You're assistance is appreciated. Many thanks Zephyr "JLatham" wrote: 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 "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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Select
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. "zephyr" wrote: Dear Mr Latham Thank you for the download on you're website. You're assistance is appreciated. Many thanks Zephyr "JLatham" wrote: 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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert date | Excel Worksheet Functions | |||
How to point to (select) a cell to the left from a cell where I enter the = equal sign? | Excel Discussion (Misc queries) | |||
Select cell, Copy it, Paste it, Return to Previous cell | Excel Discussion (Misc queries) | |||
enhanced conditional formatting | Excel Discussion (Misc queries) | |||
select a cell and the one next to it | Excel Discussion (Misc queries) |