Excel macro: how do I change all row heights, BUT if cell.value = bold make the cell height bigger?

Question

I'm working on a long list of data (Column B) that has been formatted using bold and indents. The bold cells contain the titles/category names and the indented cell values are the subcategories.

The row heights are all over the place. It should have been 10.5 for everything, and the bold cells/rows 15. I can change everything to 10.5, but then I need to spend quite a bit of time scrolling through the list amending the bold row heights. I've used the format painter but it's a long list and I didn't want to spend so much time on this part of the process. And now I know that I'll need to do this to another 30 documents.

Does anyone have a quicker way of doing this?

Solution

Sub setHeights()
Dim targetRange As Range
Dim targetCell As Range

    Set targetRange = Range("B:B")
    For Each targetCell In targetRange
        If Not IsEmpty(targetCell) Then
            If targetCell.Font.Bold Then
                targetCell.RowHeight = 15
            ElseIf targetCell.Font.Superscript Then
                targetCell.RowHeight = 12.75
            Else
                targetCell.RowHeight = 10.5
            End If
        End If
    Next targetCell
End Sub

You might want to change Range("B:B") to something like Table1.Range("B1:B255")

Comments

[RocketGoal]

Works like a charm, and now I know the procedure I think I have other things I can do with it. Thanks.

[RocketGoal]

Slight add-on: if I wanted to add a third criteria to the IF, such as if Superscript. Do I add another IF and EndIF?

[marg]

The order goes: If .. Then (newline), ElseIf ... Then (newline), Else (newline), End If. You can also nest Ifs as long as you start a new line after Then and end the block with End If.

[RocketGoal]

Just one last add-on: Is this correct? ElseIf targetcell.Font.superscript = True Then Targetcell.RowHeight = 12.75 Didn't want to raise a question aout this syntax issue. Thanks again for your time.

[marg]

I put your code between if and else and added a newline after Then and it worked fine. I don't see any problems. Btw. you don't need the = True comparison.

[RocketGoal]

Thanks, I'll check it out. Anyhow, I've raised another question - refering to this one - as I'm chasing how to recognise line breaks as well. And I didn't want you additional replies to be lost in the comments.

[RocketGoal]

It works for superscript cells. But, within my list unfortunately the superscript character is only part of the whole text (the number at the end of the sentence). So, it's not making it 12.75 as it's not all superscript. I'll add this to my other question

[Richard Erickson]

Welcome to SO. Please double check your code's formatting. I think you want to add in an extra line or two to get it to format correctly (SO won't let me make the edit because it is too small of an edit).