IsNumeric

Hi, i changed the code, it s a lot easier to understand that way, it looks like the Elseif doesn t apply when i encounter letters in a cell, so i get an error type mismatch in those case and i just wanted the code to color cells that have letters in colorindex 5...

hope some1 can help, thank you

If NumberButton Then
        
        'fonction if au cas où inverse est coché
        If CheckBox1.Value = True Then
    
            For Each vCellules2 In Range(RefEdit2.Value)
                For Each vCellules1 In Range(RefEdit1.Value)
                
                            
                vCellules1.Replace _
                What:=" ", Replacement:="", _
                SearchOrder:=xlByColumns, MatchCase:=True
    
                vCellules2.Replace _
                What:=" ", Replacement:="", _
                SearchOrder:=xlByColumns, MatchCase:=True
                                                                    
                    If vCellules1.Interior.ColorIndex = xlNone _
                    And vCellules2.Interior.ColorIndex = xlNone _
                    And IsNumeric(vCellules1) = True _
                    And IsNumeric(vCellules2) = True _
                    And (vCellules1.Value - (vCellules2.Value * -1)) <= (CDbl(TextBox1.Value)) _
                    And ((vCellules2.Value * -1) - (vCellules1.Value)) <= (CDbl(TextBox1.Value)) _
                    And vCellules1.Text <> "" _
                    And vCellules1.Address <> vCellules2.Address Then
                    vCellules1.Interior.Color = RGB(Rnd * 255, Rnd * 255, Rnd * 255)
                    vCellules2.Interior.Color = vCellules1.Interior.Color
                    
                    ElseIf IsNumeric(vCellules1) = False Or IsNumeric(vCellules2) = False Then
                    vCellules1.Interior.ColorIndex = 5
                    vCellules2.Interior.ColorIndex = 5
                    
                    End If
                Next vCellules1
            Next vCellules2
                
        Else
        'Else du CheckBox1 donc si inverse non coché
        
        For Each vCellules2 In Range(RefEdit2.Value)
                For Each vCellules1 In Range(RefEdit1.Value)
                                
                vCellules1.Replace _
                What:=" ", Replacement:="", _
                SearchOrder:=xlByColumns, MatchCase:=True
    
                vCellules2.Replace _
                What:=" ", Replacement:="", _
                SearchOrder:=xlByColumns, MatchCase:=True
                                    
                    If vCellules1.Interior.ColorIndex = xlNone _
                    And vCellules2.Interior.ColorIndex = xlNone _
                    And IsNumeric(vCellules1) = True _
                    And IsNumeric(vCellules2) = True _
                    And (vCellules1.Value - (vCellules2.Value)) <= (CDbl(TextBox1.Value)) _
                    And ((vCellules2.Value) - (vCellules1.Value)) <= (CDbl(TextBox1.Value)) _
                    And vCellules1 <> "" _
                    And vCellules2 <> "" _
                    And vCellules1.Address <> vCellules2.Address Then
                    vCellules1.Interior.Color = RGB(Rnd * 255, Rnd * 255, Rnd * 255)
                    vCellules2.Interior.Color = vCellules1.Interior.Color
                    
                    ElseIf IsNumeric(vCellules1) = False Or IsNumeric(vCellules2) = False Then
                    vCellules1.Interior.ColorIndex = 5
                    vCellules2.Interior.ColorIndex = 5
                                        
                    End If
                Next vCellules1
            Next vCellules2
                                             
        End If
End If
    
        
    
    Unload MatchingMacro
End Sub

message posted twice ><

Welcome to the forum.

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.

Your post does not comply with Rule 9 of our Forum RULES. If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. And please never edit a thread in which someone else has responded.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save