Excel VBA rowheigt from value in range

Question

I have an Excel-Sheet with values in column D. I would like to set the row height in relation to the value of cell D of each row. Values in D are small %-values like 0.0593 %, except of the first (D4 = 31 %) and last (D92 = 40 %)

To get the small values at a reasonable height I'd like to multiply them with 10'000 - but there comes the problem with the 409 max height.

I have a script that works until it comes to the high values so I tried a if formula. But to be frankly: I have no Idea what I am doing here... I copied it together.

So the problems: working only in the range of D5-D91 and if a value should go over 409 give him something like 15px.

Thanx for your Help!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim i As Long
  For i = 4 To Cells(Rows.Count, 1).End(xlUp).Row - 1
      With Cells(i, 4)
                    If .Cells(i, 4).Value * 10000 > 409 Then
            Rows(i).RowHeight = 12
                    Else
            Rows(i).RowHeight = Cells(i, 4).Value * 10000
            End If
      End With
   Next i

End Sub

Answer 1

Copy the below code to any standard module & Run. You may have to tweak the code as per your requirement.

Sub sample()

    Dim i As Long


        For i = 4 To Cells(Rows.Count, 1).End(xlUp).Row
            If Cells(i, 4).Value * 10000 > 409 Then
                Rows(i).RowHeight = 12
            Else
                Rows(i).RowHeight = Cells(i, 4).Value * 10000
            End If
        Next

End Sub