Consulting

Results 1 to 4 of 4

Thread: Stuck with block if without end if, totally lost.

  1. #1
    VBAX Newbie
    Joined
    Jan 2020
    Posts
    1
    Location

    Stuck with block if without end if, totally lost.

    I'm creating a dashboard. I've got two shapes oval 1 and oval 2. They are to change colors depending on the value of specific cells
    I'm getting an error block if without end if.
    What am i doing wrong here!
    Sub Worksheet_Change(ByVal Target As Range) ' If Intersect(Target, Range("E10")) Is Nothing Then Exit Sub
    If Target.Value >=-0.1And Target.Value <=0.1Then
            ActiveSheet.Shapes.Range(Array("Oval 1")).Select
                With Selection.ShapeRange.Fill
                .ForeColor.RGB = RGB(0,176,80)
                EndWith
        ElseIf Target.Value >=-0.29And Target.Value <0.29Then
            ActiveSheet.Shapes.Range(Array("Oval 1")).Select
                With Selection.ShapeRange.Fill
                .ForeColor.RGB = RGB(255,255,0)
                EndWith
        Else
             ActiveSheet.Shapes.Range(Array("Oval 1")).Select
                With Selection.ShapeRange.Fill
                .ForeColor.RGB = RGB(255,0,0)
    
                EndWith
    
    
    
        If Intersect(Target, Range("N10"))IsNothingThenExitSub
    
        If Target.Value >=-0.1And Target.Value <=0.1Then
            ActiveSheet.Shapes.Range(Array("Oval 2")).Select
                With Selection.ShapeRange.Fill
                .ForeColor.RGB = RGB(0,176,80)
                EndWith
        ElseIf Target.Value >=-0.29And Target.Value <0.29Then
            ActiveSheet.Shapes.Range(Array("Oval 2")).Select
                With Selection.ShapeRange.Fill
                .ForeColor.RGB = RGB(255,255,0)
                EndWith
        Else
             ActiveSheet.Shapes.Range(Array("Oval 2")).Select
                With Selection.ShapeRange.Fill
                .ForeColor.RGB = RGB(255,0,0)
                EndWith
    
    EndIf
    
    
    Range("A1").SelectEnd Sub
    Last edited by Paul_Hossler; 01-14-2020 at 03:01 PM.

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,873
    Location
    I added CODE tags to format your macro - you can use the [#] icon to insert them

    After inserting missing spaces and indenting, I'm guessing you probably wanted something like this

    Option Explicit
    
    
    Sub Worksheet_Change(ByVal Target As Range)
    
    
        If Target.Cells(1, 1).Address = "$E$10" Then
            If Target.Value >= -0.1 And Target.Value <= 0.1 Then
                ActiveSheet.Shapes.Range(Array("Oval 1")).Select
                With Selection.ShapeRange.Fill
                    .ForeColor.RGB = RGB(0, 176, 80)
                End With
            ElseIf Target.Value >= -0.29 And Target.Value < 0.29 Then
                ActiveSheet.Shapes.Range(Array("Oval 1")).Select
                With Selection.ShapeRange.Fill
                    .ForeColor.RGB = RGB(255, 255, 0)
                End With
            Else
                ActiveSheet.Shapes.Range(Array("Oval 1")).Select
                With Selection.ShapeRange.Fill
                    .ForeColor.RGB = RGB(255, 0, 0)
                End With
            End If
    
    
        ElseIf Target.Cells(1, 1).Address = "$N$10" Then
            If Target.Value >= -0.1 And Target.Value <= 0.1 Then
                ActiveSheet.Shapes.Range(Array("Oval 2")).Select
                With Selection.ShapeRange.Fill
                    .ForeColor.RGB = RGB(0, 176, 80)
                End With
            ElseIf Target.Value >= -0.29 And Target.Value < 0.29 Then
                ActiveSheet.Shapes.Range(Array("Oval 2")).Select
                With Selection.ShapeRange.Fill
                    .ForeColor.RGB = RGB(255, 255, 0)
                End With
            Else
                ActiveSheet.Shapes.Range(Array("Oval 2")).Select
                With Selection.ShapeRange.Fill
                    .ForeColor.RGB = RGB(255, 0, 0)
                End With
            End If
        End If
    
    
        Range("A1").Select
    End Sub

    But I think you could simplify

    Option Explicit
    
    
    
    
    Sub Worksheet_Change(ByVal Target As Range)
    
        Dim shpOval As Shape
        Dim r As Range
        
        Set r = Target.Cells(1, 1)
    
    
        If r.Address = "$E$10" Then
            Set shpOval = ActiveSheet.Shapes("Oval 1")
        ElseIf r.Address = "$N$10" Then
            Set shpOval = ActiveSheet.Shapes("Oval 2")
        Else
            Exit Sub
        End If
        
        If r.Value >= -0.1 And r.Value <= 0.1 Then
            shpOval.Fill.ForeColor.RGB = RGB(0, 176, 80)
        ElseIf r.Value >= -0.29 And r.Value < 0.29 Then
            shpOval.Fill.ForeColor.RGB = RGB(255, 255, 0)
        Else
            shpOval.Fill.ForeColor.RGB = RGB(255, 0, 0)
        End If
    
    
        Range("A1").Select
    End Sub
    Last edited by Paul_Hossler; 01-14-2020 at 03:23 PM.
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,051
    Location
    My style, working from Paul's code
    Option Explicit
    
    Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells(1, 1).Address = "$E$10" Then ColorShape Target, "Oval 1"
        If Target.Cells(1, 1).Address = "$N$10" Then ColorShape Target, "Oval 2"
        
        Range("A1").Select
    End Sub
    Private Sub ColorShape(ByVal Target As Range, ShpName As String)
       If Target.Value >= -0.1 And Target.Value <= 0.1 Then
           ActiveSheet.Shapes.Range(Array(ShpName)).Select
           With Selection.ShapeRange.Fill
               .ForeColor.RGB = RGB(0, 176, 80)
           End With
       ElseIf Target.Value >= -0.29 And Target.Value < 0.29 Then
           ActiveSheet.Shapes.Range(Array(ShpName)).Select
           With Selection.ShapeRange.Fill
               .ForeColor.RGB = RGB(255, 255, 0)
           End With
       Else
           ActiveSheet.Shapes.Range(Array(ShpName)).Select
           With Selection.ShapeRange.Fill
               .ForeColor.RGB = RGB(255, 0, 0)
           End With
       End If
    End Sub
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,715
    Always avoid 'Select' in VBA.

    Sub Worksheet_Change(ByVal Target As Range)
      If InStr("$E$10$N$10", Target.Address) = 0 Then Exit Sub
    
      y = Target.Value
      With ActiveSheet.Shapes("Oval " & 1 - (Target.Address = "$N$10")).Fill.ForeColor
        .RGB = RGB(255, 255 * -(y >= -0.29 And y < 0.29), 0)
        If y >= -0.1 And y <= 0.1 Then .RGB = RGB(0, 176, 80)
      End With
    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •