Formula based on cell formatting

[Miguelluis]

Hi,

I've got a sheet with data as follows

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Name 1[/TD]
[TD]Name 2[/TD]
[TD]Name 3[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Passport[/TD]
[TD]DL[/TD]
[TD]Utility[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]Passport[/TD]
[TD]DL
[/TD]
[TD]Utility[/TD]
[/TR]
[TR]
[TD]778[/TD]
[TD]Passport[/TD]
[TD]DL[/TD]
[TD]Utility[/TD]
[/TR]
</tbody>[/TABLE]

I wanted to concatenate only if the cells or font is formatted with the colour red and list the items in separate lines, like below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1234[/TD]
[TD]-Passport
-DL
-Utility[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]-Passport[/TD]
[/TR]
[TR]
[TD]778[/TD]
[TD]-DL
-Utility[/TD]
[/TR]
</tbody>[/TABLE]

Assuming that the above correspond to the cells that were highlighted.

Is there a way of doing this?
Thanks
Miguel

 

[Joe4]

Not without VBA, I believe.
Native Excel functions run on values, not formatting.

However, I do not understand your example. I do not see anything highlighted in red, so I am afraid that your example isn't real clear.

 

[Miguelluis]

Not without VBA, I believe.
Native Excel functions run on values, not formatting.

However, I do not understand your example. I do not see anything highlighted in red, so I am afraid that your example isn't real clear.

Hi, Was having issues with highlighting.

---

[TABLE="width: 414"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Client[/TD]
[TD]Name 1[/TD]
[TD]Name 2[/TD]
[TD]Name 3[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Passport[/TD]
[TD]DL[/TD]
[TD]Utility[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]Passport[/TD]
[TD]DL[/TD]
[TD]Utility[/TD]
[/TR]
[TR]
[TD]778[/TD]
[TD]Passport[/TD]
[TD]DL[/TD]
[TD]Utility[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

So, is VBA an acceptable solution for you?
Do you want this to happen in the same place, or on another sheet?
If in the same place, should the unhighlighted values simply be erased?
What if a line has a client, but no highlighted items? Do you want to show that Client code, or should that whole line be removed?

 

[Miguelluis]

VBA could work, I want it to pull the data to a separate sheet if highlighted only. Any cells that are not highlighted that client code/line doesn't pull through the new sheet.

 

[Joe4]

I think this code should do what you want. You may just need to change the two sheet names at the top of the code.

Sub MyCopyMacro()

    Dim srcSht As Worksheet
    Dim dstSht As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim nr As Long
    Dim c As Long
    Dim client As String
    Dim nme As String
    Dim ct As Long
        
'   Set source and desination sheets
    Set srcSht = Sheets("Sheet1")
    Set dstSht = Sheets("Sheet2")
    
    Application.ScreenUpdating = False
    
'   Put headers in destination sheet
    dstSht.Range("A1") = "Client"
    dstSht.Range("B1") = "Name"
    
'   Find last row with data in column A on source sheet
    lr = srcSht.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows from row 2 to end in source sheete
    For r = 2 To lr
        client = srcSht.Cells(r, "A")
'       Loop through columns 2-4
        ct = 0
        For c = 2 To 4
'           See if any fonts are red
            If srcSht.Cells(r, c).Font.Color = vbRed Then
                nme = srcSht.Cells(r, c)    'capture name
                nr = dstSht.Cells(Rows.Count, "B").End(xlUp).Row + 1    'capture new row
                ct = ct + 1     'capture count for this client
'               Populate client on destination sheet if first match for this client
                If ct = 1 Then dstSht.Cells(nr, "A") = client
'               Populate name on destination sheet
                dstSht.Cells(nr, "B") = nme
            End If
        Next c
    Next r
    
    Application.ScreenUpdating = True
                        
End Sub

 

[Miguelluis]

Thank you!

 

[Joe4]

You are welcome!