How do you alternate text colour by each person name

[Fanta0575]

Hi I want to be able to apply a custom format to be able to do this
Jane Seymour Stephen king Dean koontz Ian rankin

Basically alternate colour of each set of forename and surname
Is this possible?

 

[etaf]

can you have a helper column to put a 1 or 0 in and then use that for conditional formatting ?

you can use
=IF(A2=A1,E1,1-E1) - where A is the name and E is where the helper column is
then use that column to apply conditional formatting for the row

Highlight Catergories with alternate colours.xlsx
	A	B	C	D	E
2	Name1				1
3	Name1				1
4	name2				0
5	Name2				0
6	name2				0
7	name2				0
8	name2				0
9	Name3				1
10	Name4				0
11	Name5				1
12	Name5				1
13	name5				1
14	Name6				0
15	Name7				1
16	Name8				0
17	Name9				1
Sheet3

Cell Formulas
Range	Formula
E2:E17	E2	=IF(A2=A1,E1,1-E1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E17	Expression	=$E2=0	text	NO
A2:E17	Expression	=$E2=1	text	NO

 

[Fanta0575]

can you have a helper column to put a 1 or 0 in and then use that for conditional formatting ?

you can use
=IF(A2=A1,E1,1-E1) - where A is the name and E is where the helper column is
then use that column to apply conditional formatting for the row

Highlight Catergories with alternate colours.xlsx
	A	B	C	D	E
2	Name1				1
3	Name1				1
4	name2				0
5	Name2				0
6	name2				0
7	name2				0
8	name2				0
9	Name3				1
10	Name4				0
11	Name5				1
12	Name5				1
13	name5				1
14	Name6				0
15	Name7				1
16	Name8				0
17	Name9				1
Sheet3

Cell Formulas
Range	Formula
E2:E17	E2	=IF(A2=A1,E1,1-E1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E17	Expression	=$E2=0	text	NO
A2:E17	Expression	=$E2=1	text	NO

No all names need to be in same cell

 

[Fanta0575]

No all names need to be in same cell

 

[etaf]

Oh, OK , sorry , you will need VBA to that , not my area for providing answers here, to out of date

 

[Eric W]

Assuming your names start in B3 and continue down, try this:

Sub HiliteAlternateNames()
Dim r As Long, s As String, Loc As Long, LocA As Long, MyLen As Long

    For r = 3 To Range("B3").End(xlDown).Row
        s = Cells(r, "B").Value
        s = Replace(s, Chr(10), " ")
        s = WorksheetFunction.Trim(s)
        Cells(r, "B") = s
        s = s & " "
        Loc = 1
        While Loc > 0
            Loc = InStr(Loc + 1, s, " ")
            If Loc > 0 Then Loc = InStr(Loc + 1, s, " ")
            If Loc > 0 Then
                LocA = Loc + 1
                Loc = InStr(Loc + 1, s, " ")
                If Loc > 0 Then Loc = InStr(Loc + 1, s, " ")
            End If
            MyLen = Loc - LocA
            If MyLen < 0 Then MyLen = 100
            Cells(r, "B").Characters(Start:=LocA, Length:=MyLen).Font.Color = vbRed
        Wend
    Next r
            
End Sub

Be aware that initials, titles, 2 part surnames or first names will throw a monkey wrench in this. Perhaps use a dash in such cases.