Errors on If Statements

**ChemistB** · 08-04-2008, 01:21 PM

I'm trying to write code for henro8
http://www.excelforum.com/showthread.php?t=651731

and I get the following error with both nested if statements

Compile error: End if without block if

I tried with the Elseif and with separate if's but got the same error. What am I doing wrong?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngCell As Range
Dim DrpDown As String

If Target.Column = 1 And Target.Row = 5 Then
    DrpDown = Target.Value
    Set RngCell = Cells(2, 5)
    
    If DrpDown = "$/square foot" Then
    With RngCell
        .NumberFormat = "$#,##0.00"
  
    ElseIf DrpDown = "%/construction cost" Then
    With RngCell
        .NumberFormat = "0.0%"
    End If
    
End If
End Sub

Thanks in advance!

ChemistB

**shg** · 08-04-2008, 01:25 PM

You're missing an "End With", but the With statements don't contribute anything here, Chemist.

Maybe just

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A5" Then
        Select Case Target.Value
            Case "$/square foot"
                Range("E2").NumberFormat = "$#,##0.00"
            Case "%/construction cost"
                Range("E2").NumberFormat = "0.0%"
        End Select
    End If
End Sub

**BigBas** · 08-04-2008, 01:25 PM

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngCell As Range
Dim DrpDown As String

If Target.Column = 1 And Target.Row = 5 Then
    DrpDown = Target.Value
    Set RngCell = Cells(2, 5)
End If
    
    If DrpDown = "$/square foot" Then
    With RngCell
        .NumberFormat = "$#,##0.00"
  
    ElseIf DrpDown = "%/construction cost" Then
    With RngCell
        .NumberFormat = "0.0%"
    End If
    
End Sub

EDIT: You also didn't "End With" in either case.