concatenate cell of certain color

[mshunt13]

I have a cell range, G4:AK4, that I would like to concatenate only the cells that are a certain color. The value of the cells are number. I would also like to include a coma between each number.

 

[Scott Huish]

When you say they are a certain color, do you mean the cell background or the color of the font?
What color?

 

[mshunt13]

The cell background. Right now the color is a gray color but it can be changed to any color.

 

[Scott Huish]

OK, here's a UDF for you that will do that.
The first parameter is for a cell that holds the color you want to check for and the second is your number range.
Note that if you change a color, you will have to hit F9 to recalculate it.

Function ConcatenateByColor(srcColor As Range, NumberRange As Range)
Application.Volatile
Dim clr As Long, c As Range, s As String
clr = srcColor.Interior.Color
For Each c In NumberRange
    If c.Interior.Color = clr And IsNumeric(c) And c <> "" Then s = s & c & ","
Next
If Len(s) Then ConcatenateByColor = Left(s, Len(s) - 1)
End Function

Book2
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
4						73	50	4	65	96		56	21	12					84		62			41	51		52	6	26		64
5
6	73,65,96,56,21,12,84,62,41,51,52,6,26,64,99,85
7
Sheet1

Cell Formulas
Range	Formula
D6	D6	=concatenatebycolor(D4,G4:AK4)

 

[mshunt13]

thanks. This works well with manually colored filled cells. Is there a way to make it work with conditional formatting?

 

[Peter_SSs]

Is there a way to make it work with conditional formatting?

What is the conditional formatting rule that colours the cells?

 

[mshunt13]

use a formula to determine which cells to format

 

[Peter_SSs]

use a formula to determine which cells to format

Yes, but what is the formula?

It is quite likely that the same formula, or at least the logic behind it, can be used to do your count, rather than actually looking for and counting the coloured cells.

 

[mshunt13]

if cell = "A", "True", "False", the cell changes to blue if true.

Row 1 has numbers 1 through 31. Row 2, 3 and 4 compares data. If the data does not match in rows 2-4, then row 7 another indicates the discrepancy with an "A". If there is an "A" in row 7 we have row 1 (with numbers 1-31) conditionally formated to change colors. To the left of row 1, i am using the concatenate information provided in this thread however it will only work if I manually color fill the cells.

 

[Peter_SSs]

if cell = "A", "True", "False"

That is not a Conditional Formatting formula and the rest of the explanation is confusing given that we are not familiar with your data.
Can you post some actual sample data with the actual Conditional Formatting formula with XL2BB so that we can see the data, the CF formula(s) and copy it for for testing?