I have this custom VBA function

Public Function concatenatespecial(rng As Range) As String

Dim rng1 As Range

concatenatespecial = ""

   For Each rng1 In rng
        If (Not Rows(rng1.Row).Hidden) And (rng1.Value <> "") And (Not rng1.Interior.ColorIndex = -4142) Then
            concatenatespecial = concatenatespecial & rng1.Text & "|"
        End If
   Next rng1

End Function

It takes all yellow and non blank cells in range and concatenate it with "|".

Problem is that, it works only if these cells are colored manually. When it is colored by conditional formatting, it does not work.

Could you help me with solution?

Better explanation:

enter image description here

  • 2
    Change rng1.Interior.ColorIndex to rng1.DisplayFormat.Interior.ColorIndex – Scott Craner May 11 '17 at 19:42
  • @MateJuhasz: I little stucked. My condition in conditional formatting is: =AND('Psaní, nakládka, závoz'!$I8="X";'Psaní, nakládka, závoz'!$H8="R"). I tried to apply it into VBA code: And (Sheets("Psaní, nakládka, závoz").Range(I8) = "X") And (Sheets("Psaní, nakládka, závoz").Range(H8) = "R") Then... But there is problem with that exact range I think. How to make it better? @ScottCraner: Unfortunately it does not work. Makes only #VALUE error. – Roman Žydyk May 11 '17 at 19:55
  • Maybe this could help somehow? link – Roman Žydyk May 11 '17 at 21:04
  • Possibly extract the color using the FormatConditions object and then test both colors (assuming that a manual color does not influence the formatconditions return value)? ( msdn.microsoft.com/en-us/library/office/ff196650.aspx ) – Yorik May 11 '17 at 21:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.