In column A, I have a list of names.
The names repeat sometimes, depends on the data.
.
I want to format the list by bolding every other name.
Is there a way to automate this bold formatting?
The lists will likely grow and more names will be added that I cannot anticipate so I need Excel to apply this formatting as more names are added.
How about using conditional formatting (I assume your data is sorted):
Mark the column A and add a conditional formatting using a formula. Add as formula:
=ISEVEN(COUNTA(UNIQUE($A$1:$A1)))
Note that the range starts always at row 1, but ends for every cell in column A at the current row (for cell A4, the range would be implicitly $A$1:$A4, for A10 it would be $A$1:$A10 ...).
UNIQUE returns a list of unique values; COUNTA will count this unique list; and ISEVEN will return True when the number of unique values is even.
Assuming that your data has a header, for all the cells holding the first name (John), the number of unique values is 2 (header and "John") and therefore the name is formatted in bold. For the next name (Nancy), the number of unique values is 3 which is odd: No formatting is done.
Note that this will work only for modern version of Excel (eg 365) as the UNIQUE-function was added only recently.
Of course you can also use code for that. Big difference: Conditional formatting adapts when you change your data, you don't have to do anything. When you format the cells with Bold (by hand or by code) and change your data, you have to redo your work (eg run the macro another time).
Just to get you the idea:
Sub MakeMyNamesBold()
Dim r As Range
Set r = ThisWorkbook.Sheets(1).Range("A1").CurrentRegion.Columns(1)
Dim cell As Range, isEven As Boolean
For Each cell In r.Cells
If cell.Row = 1 Then
isEven = False
Else
If cell.Value <> cell.Offset(-1, 0).Value Then
isEven = Not isEven
End If
End If
cell.Font.Bold = isEven
Next cell
End Sub
Update To make a complete row bold:
For conditional formatting: Just apply this conditional format rule to all your data. The column address in the formula is absolute ($A), the count/unique logic will look to column A for all the cells.
When using the code, you simply change the line that sets the Bold-attribute to
cell.EntireRow.Font.Bold = isEven
I would also suggest conditional formatting, just like @FunThomas, but with a slightly different rule, namely:
=ISEVEN(SUM(--(A$1:A1<>A$2:A2)))
for a formatting range starting with A2.
You can use ISODD instead of ISEVEN.
The difference is that my formula records every name change in a column without checking for uniqueness. If a name occurs again in another block, the formatting will be different.
To format several columns based on column A, you need to add $ characters to the formula:
=ISEVEN(SUM(--($A$1:$A1<>$A$2:$A2)))
Assuming the data follows the same order as shown in the post, identical names appear in consecutive blocks.
Sub Demo()
Dim Sht As Worksheet: Set Sht = Sheets("Sheet1")
With Sht.Range("A1").CurrentRegion
' .Sort key1:=.Cells(1), Header:=xlYes
.Font.Bold = False
Dim ar: ar = .Value
End With
Dim oDic As Object: Set oDic = CreateObject("scripting.dictionary")
Dim r As Range, i As Long, sKey As String
For i = LBound(ar) + 1 To UBound(ar)
sKey = Trim(ar(i, 1))
If Not oDic.exists(sKey) Then
oDic(sKey) = Empty
End If
If oDic.Count Mod 2 = 1 Then
If r Is Nothing Then
Set r = Sht.Cells(i, 1)
Else
Set r = Application.Union(r, Sht.Cells(i, 1))
End If
End If
Next
If Not r Is Nothing Then
r.Font.Bold = True
End If
End Sub
If the data rows are in random order and the expected output doesn’t require a specific name order, you can add the Sort (the 4th line in the script). The resulting output will appear as shown below.
