Add a search for numeric data only RRS feed

  • Question

  • Hi, I have created this code but want to add a ISNUMERIC data search parameter so it can be used in sheets with alphanumeric data. I have tried adding a while and do loop and another IF statement but can't seem to et it t work. Any suggestions realty appreciated.

    Sub Macro1()
    ' Macro1 Macro
    ' Keyboard Shortcut: Ctrl+q

    ' Stage 1 open file

     ' Stage 2 find odd/even Highlight
       Dim myData As Range
       Dim myCell As Range
       Set myData = ActiveSheet.UsedRange                       ' need to filter data to only search numbers
       For Each myCell In myData                                 ' If sheet contains alphanumeric data it fails
       If myCell.Value Mod 2 = 0 And myCell.Value > 0 Then
       myCell.Interior.ColorIndex = 37
       ElseIf myCell.Value Mod 2 = 1 Then
       myCell.Interior.ColorIndex = 4
       End If

    ' Stage 4 Add and Average

        Dim mySheet As Range

        Dim myAverage As Integer

            Set mySheet = ActiveSheet.UsedRange

            With ActiveSheet

                Application.Sum (mySheet)
                myAverage = WorksheetFunction.Average(mySheet)

                    MsgBox "Average is " & myAverage
            End With
    End Sub

    Tuesday, October 6, 2015 9:32 PM