Conditional formatting - Highlighting Cells containing text from an array of nam

Question

Conditional formatting - Highlighting Cells containing text from an array of names

Good day everyone,

I can't see any option in conditional formatting where i can highlight the cells in ColumnA having names from ColumnC.

I have about 1,000 cells to be identified having those names on the columnC. Once identified, the date and time beside the

name needs to be extracted to ColumnB.

> Filtering can do only 2 entries.

> Using Conditional formating prompts that array of such reference could not be used.

Please help. I'm getting confused. Doing it manually is really crazy.

A B C 30/07/14 20:55:22 (Sebastian): 30/07/14 17:05:03 (Linda): Leonardo 29/07/14 17:55:22 (Sebastian): 29/07/14 08:55:01 (Bernard): Dean 28/07/14 07:35:12 (Sebastian): 28/07/14 02:10:31 (Leo): Sandra 27/07/14 19:18:25 (Sebastian): 27/07/14 16:43:20 (Romeo): Nicole 26/07/14 23:12:11 (Sebastian): 26/07/14 17:40:36 (Mark): Marshal 25/07/14 12:13:45 (Sebastian): 25/07/14 11:51:29 (Marshal): Brian 24/07/14 20:05:42 (Sebastian): 24/07/14 18:51:27 (Nicole): 23/07/14 15:05:03 (Sebastian): 23/07/14 11:50:20 (Cindy): 22/07/14 14:26:11 (Sebastian): 22/07/14 10:55:01 (Brian): 21/07/14 09:31:14 (Sebastian): 21/07/14 07:53:25 (Dean): 20/07/14 20:05:52 (Sebastian): 20/07/14 16:23:11 (Mike): 19/07/14 17:21:29 (Sebastian): 19/07/14 13:32:42 (Sandra): 18/07/14 16:00:20 (Sebastian): 18/07/14 14:23:13 (Leonardo): 17/07/14 08:51:19 (Sebastian): 17/07/14 03:31:44 (Lester): 16/07/14 09:05:23 (Sebastian): 16/07/14 02:55:39 (Kevin): 15/07/14 11:32:11 (Sebastian): 15/07/14 09:21:49 (Lance): 14/07/14 18:21:02 (Sebastian): 14/07/14 17:19:14 (Rio): 13/07/14 19:31:34 (Sebastian): 13/07/14 16:15:52 (Sean):

Explanation / Answer

.,How about a macro?/

Sub Macro()
Dim r As Long
Dim c As Long
Dim v As Variant
Dim i As Integer
  
For r = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For c = 1 To Cells(Rows.Count, 3).End(xlUp).Row
If Cells(r, 1).Value Like "*" & Cells(c, 3).Value & "*" Then
Cells(r, 1).Interior.ColorIndex = 3
v = Split(Cells(r, 1).Value, " ")
For i = 2 To UBound(v) Step 3
If v(i) Like "*" & Cells(r, 3).Value & "*" Then
Cells(r, 2).Value = Cells(r, 2).Value & v(i - 2) & " " & v(i - 1) & " "
End If
Next i
End If
Next c
Next r
End Sub

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.