Row Height adjusted based on content via code or Macro

[SBass]

I have a worksheet that contains two cells that are populated via a series of nested IF statements based on the contents of another cell. The issue is that one of the statements generates a MUCH longer text string than the other two options. To make the text appear aesthetically pleasing, the rows need to be at a height of 40 for the longer string, but only 15 for the other strings. I realize that I can't accomplish this with conditional formatting, but there has to be a way to do it. I'm open to ideas/suggestions, even if it includes simple code or a macro.

 

[mumps]

I thought that is what you wanted according to what you said in Post #5 :

Cell A73 populates based on the content of B8.

 

[SBass]

I am using a nested IF statement to populate A73 based on B8.

 

[mumps]

Delete this row:

Range("A73") = Target

 

[SBass]

That makes the text wrap, but it doesn't change the row height based on the content.

 

[mumps]

Can you post your code?

 

[SBass]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B8")) Is Nothing Then Exit Sub
If Len(Target) > 9 Then
Range("A73").WrapText = True
End If
End Sub

Here is the Nested IF
=IF(B8="Business Management"," MBA-5121",IF(B8="Business Leadership", "MSOL-5104",IF(B8="Technology and Innovation Management","TIM-6120, TIM-6310, TIM-6510, TIM-6210, OR TIM-6410","")))

 

[mumps]

Remove the nested if formula you currently have in A73 and try this macro in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B8")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Business Management"
            Range("A73") = "MBA-5121"
        Case "Business Leadership"
            Range("A73") = "MSOL-5104"
        Case "Technology and Innovation Management"
            Range("A73") = "TIM-6120, TIM-6310, TIM-6510, TIM-6210, OR TIM-6410"
            Range("A73").WrapText = True
    End Select
End Sub

 

[SBass]

Thank you for taking the time to help with this.

The latest code does the same thing as the Nested IF. It still doesn't adjust the row height.

 

[mumps]

When I tried it on a dummy sheet it worked for me. I'm not sure how your data is organized. Can you post a sample of your sheet? Follow this link for instructions (Section B): https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[SBass]

Excel 2012
	A	B
8
73
74
BBA

Cell Formulas
Range	Formula
A73	=IF(B8="Business Management"," MBA-5110",IF(B8="Business Leadership", "MSOL-5103",IF(B8="Technology and Innovation Management","TIM-6101, TIM-6110, TIM-6301, TIM-6501, TIM-6601, OR TIM-5010","")))
A74	=IF(B8="Business Management"," MBA-5121",IF(B8="Business Leadership", "MSOL-5104",IF(B8="Technology and Innovation Management","TIM-6120, TIM-6310, TIM-6510, TIM-6210, OR TIM-6410","")))