Font and Row Height with multi with or if statements

**sas2007** · 08-21-2012, 12:52 PM

I am able to change the font size of my text based on location. However, I am unable to change the row size based on the same If Then criteria. I am unsure what I am doing wrong. any suggestions?!

I keep getting the With object must be user-defined type, Object, or Variant error

 For Each s In Sheets

    s.Select
    Cells.Select
   
    With Selection
   
    End With

    For Each cll In ActiveSheet.Range("J101:J110")
        If Range("J101") = "Here!" Then
            With Range("A101:A113").FONT
            .Size = 12
            .Name = "Times New Roman"
            .Bold = False
            End With
            
        If Range("J101") = "Here!" Then
            With Range("A101:A113").Row
                Cells.RowHeight = 15.75
        End With

 End If
    End If
   Next
Next s
 
End Sub

#vba pro in training

**Mordred** · 08-21-2012, 01:04 PM

Hi sas2007, try this slightly modified version of your code

Sub FormatCells()
    Dim s As Worksheet
    For Each s In Sheets
        For Each cll In s.Range("J101:J110")
            If s.Range("J101").Value = "Here!" Then
                With s.Range("A101:A113").Cells.Font
                    .Size = 12
                    .Name = "Times New Roman"
                    .Bold = False
                End With
            End If
            If s.Range("J101") = "Here!" Then
                With s.Range("A101:A113").Rows
                    s.Cells.RowHeight = 15.75
                End With
            End If
        Next cll
    Next s
End Sub

**sas2007** · 08-21-2012, 01:32 PM

Thanks that really helps. the only thing is that it sets all my rows to 15.75. The rows above A101 are set to 19.

I did add the value statement to the second ("J101") line

Sub FormatCells()
    Dim s As Worksheet
    For Each s In Sheets
        For Each cll In s.Range("J101:J110")
            If s.Range("J101").Value = "Here!" Then
                With s.Range("A101:A113").Cells.Font
                    .Size = 12
                    .Name = "Times New Roman"
                    .Bold = False
                End With
            End If
            If s.Range("J101").Value = "Here!" Then
                With s.Range("A101:A113").Rows
                    s.Cells.RowHeight = 15.75
                End With
            End If
        Next cll
    Next s
End Sub

**Mordred** · 08-21-2012, 01:38 PM

Does that solve it?

**sas2007** · 08-21-2012, 01:41 PM

no all the row heights are setting to 15.75. I need only the cells that are set to font 12 to be set to row height 15.75. the other cells should be height=19

**Mordred** · 08-21-2012, 01:51 PM

Try

Sub FormatCells()
    Dim s As Worksheet
    For Each s In Sheets
            If s.Range("J101").Value = "Here!" Then
                With s.Range("A101:A113").Cells.Font
                    .Size = 12
                    .Name = "Times New Roman"
                    .Bold = False
                End With
            End If
            If s.Range("J101").Value = "Here!" Then
                With s.Range("A101:A113").Cells.Rows
                    .RowHeight = 15.75
                End With
            End If
    Next s
End Sub