Results 1 to 14 of 14

Can't get command buttons to run sub and clear output contents

Threaded View

  1. #1
    Registered User
    Join Date
    11-10-2011
    Location
    Chatsworth, California
    MS-Off Ver
    Excel 2010
    Posts
    41

    Can't get command buttons to run sub and clear output contents

    I am trying to place command buttons in the subroutine below for (1) executing the subroutine, and then for (2) clearing the contents of column D. The problem is that the original subroutine has formula “=C4-B4” (and copied down) entered into column D for the proper execution of the original subroutine, and a command button to clear column D will erase this formula.

    * See 1st attachment without any extra code for command buttons &
    ** 2nd attachment with my attempts to include the command buttons "Execute" & "Clear"

     ‘The subroutine computes the difference in test scores (columns B & C) into column D 
     '(if “=B4-C4” is entered into D4 and copied down each time the subroutine is run)
     ‘The subroutine then highlights highest score in green and lowest score in pink.
    
    Public Sub TestDiff()
    
        Dim Test1 As Range, Test2 As Range
        Dim dMax As Double, dMin As Double
        Dim rCell As Range, rDiff As Range
    
        Set Test1 = Range("B4:B13")
        Set Test2 = Range("C4:C13")
    
                        'Formula for column D {D4: =C4-B4… copied down to D13}……. 
    
         Set rDiff = Range("D4:D13")
    
        dMax = Application.WorksheetFunction.Max(rDiff)
        dMin = Application.WorksheetFunction.Min(rDiff)
    
        For Each rCell In rDiff
            'Color cell interior green'
            If rCell.Value = dMax Then
                rCell.Interior.ColorIndex = 4
            End If
    
            'Color cell interior pink'
            If rCell.Value = dMin Then
                rCell.Interior.ColorIndex = 7
            End If
        Next rCell
    
    End Sub
    Name Test #1 Score Test #2 Score Test Score Differences
    Bob Fenton 67 53 -14
    Mary Right 75 89 14
    Gale Hernandez 98 95 -3
    Mark Deutsch 89 82 -7
    Gary Miles 72 73 1
    Fred Flinstone 99 85 -14
    Barry Gillian 83 79 -4
    Nancy Aguirre 85 72 -13
    Sharon Fox 79 80 1
    Martin Noonan 59 58 -1
    Last edited by BGiffin2004; 11-20-2011 at 10:42 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1