title: VBA concatenate selected cells filling a condition
body: <p>I am trying to write a function in VBA that allows me to concatenate cells and adding a "," in between elements. The other aspect of this function is that I want only to concatenate the cells that are the same fill color and font color as the first in the range selected. (My spreadsheet has a list of labels that are in cells with different colors and font colors).
  If found a code online that works marvels w/o the conditions. But when I try to add them, it returns a value error.
  Here is my function:</p>
  
  <pre><code>Function Concat(rng As Range) As String 
       Dim rngCell As Range
       Dim strResult As String
       Dim bcolor As Long
       Dim fcolor As Long
       bcolor = rng.Cells(1, 1).Interior.ColorIndex
       fcolor = rng.Cells(1, 1).Font.ColorIndex
       For Each rngCell In rng
           If rngCell.Value &lt;&gt; "" And rngCell.Interior.ColorIndex = bcolor And rngCell.Font.ColorIndex = fcolor Then
              strResult = strResult &amp; "," &amp; rngCell.Value
           End If
       Next rngCell
       If rngCell.Value &lt;&gt; "" And rngCell.Interior.ColorIndex = rng.Cells(1, 1).Interior.ColorIndex And rngCell.Font.ColorIndex = rng.Cells(1, 1).Font.ColorIndex Then
           strResult = Mid(strResult, Len(",") + 1)
       End If
       Concat = strResult
  End Function
  </code></pre>
  
  <p>I am very new to VBA (I started this afternoon) so the reason I added the bcolor and fcolor was for debugging. Actually I think there is something fundamental i don't understand in VBA because even the following function doesn't return any value:</p>
  
  <pre><code>Function Concat(rng As Range) As Long 'Replace "Long" by "String" after debug is over
       Dim rngCell As Range
       Dim strResult As String
       Dim bcolor As Long
       Dim fcolor As Long
       bcolor = rng.Cells(1, 1).Interior.ColorIndex
       fcolor = rng.Cells(1, 1).Font.ColorIndex
       For Each rngCell In rng
           If rngCell.Value &lt;&gt; "" And rngCell.Interior.ColorIndex = bcolor And rngCell.Font.ColorIndex = fcolor Then
              strResult = strResult &amp; "," &amp; rngCell.Value
           End If
       Next rngCell
       If rngCell.Value &lt;&gt; "" And rngCell.Interior.ColorIndex = rng.Cells(1, 1).Interior.ColorIndex And rngCell.Font.ColorIndex = rng.Cells(1, 1).Font.ColorIndex Then
           strResult = Mid(strResult, Len(",") + 1)
       End If
       Concat = bcolor
  End Function
  </code></pre>
  
  <p>It really bugs me that this function doesn't return the color of the cell while he following does:</p>
  
  <pre><code>Function color1(rng As Range) As Long
      color1 = rng.Cells(1, 1).Font.ColorIndex
  End Function
  </code></pre>
  
  <p>I know there is something fundamental I don't understand about VBA coding here. But I can't tell what. If you see what is wrong, I would appreciate a correction and an explanation of what my mistake is.
  Thanks!
  Xavier</p>