[X]My Assistant

[Miguel_A]

Hi all

I have an excel sheep with a table that starts at row 2 (being the row 2 titles and below it all the data).
At row one it's suppose to be a search input, so text can be input on them and bellow it filters the data that contains that input (with multiple criteria or not), however when working in a column that have just numbers i cannot figured out a way to filter that at all.
Can you guys explain why numbers don't work the same way has text and can you guys provide with a solution ?

It's the column F3+ so F1 is the input for the number contain input

Have provided with a file example with some data (data is not real) and with a image to show here to be faster

One more thing, if there is a better way of doing this i would appreciate the explanation and i will try to do it.

This will be to hold some data monthly (something like 1000 lines)

thanks in advance

 queimas_ing.png ( 41.32K )Number of downloads: 8

CODE

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect
    Application.EnableEvents = False
    
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim DataRange As Range

    Set sht = ActiveSheet
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    
    Set DataRange = Range("A3:J" & LastRow)
    
    If Target.Row = 1 Then
        If Target.Column > 1 Then
            If Target.Column = 5 Then
                Cells(Target.Row, 6).NumberFormat = "General"
                
                Cells(Target.Row, 9).NumberFormat = "General"
                Cells(Target.Row, 9).Value = "State whatever"
            End If
        End If
        DataRange.AutoFilter Field:=1, VisibleDropDown:=False
        DataRange.AutoFilter Field:=2, VisibleDropDown:=False
        DataRange.AutoFilter Field:=3, VisibleDropDown:=False
        DataRange.AutoFilter Field:=4, VisibleDropDown:=False
        DataRange.AutoFilter Field:=5, Criteria1:="*" & Cells(Target.Row, 5).Value & "*", VisibleDropDown:=False
        DataRange.AutoFilter Field:=6, VisibleDropDown:=False
        DataRange.AutoFilter Field:=7, Criteria1:="*" & Cells(Target.Row, 7).Value & "*", VisibleDropDown:=False
        DataRange.AutoFilter Field:=8, Criteria1:="*" & Cells(Target.Row, 8).Value & "*", VisibleDropDown:=False
        DataRange.AutoFilter Field:=9, VisibleDropDown:=False
        If Target.Column = 10 Then
            If Target.Value = vbNullString Then
                DataRange.AutoFilter Field:=10, VisibleDropDown:=False
            Else
                DataRange.AutoFilter Field:=10, Criteria1:=Cells(Target.Row, 10).Value, VisibleDropDown:=False
            End If
        End If
    End If
    If Target.Row = 2 Then
        Range("A" & Target.Row & ":J" & Target.Row).Locked = True
    End If
    If Target.Row > 2 Then
        If Target.Column = 5 Then
            Cells(Target.Row, 1).NumberFormat = "0"
            Cells(Target.Row, 1).Value = Target.Row - 2
            
            Cells(Target.Row, 2).NumberFormat = "yyyy-mm-dd"
            Cells(Target.Row, 2).Value = Now
            
            Cells(Target.Row, 3).NumberFormat = "hh:mm"
            Cells(Target.Row, 3).Value = Now
            
            Cells(Target.Row, 6).NumberFormat = "0"
            
            Cells(Target.Row, 9).NumberFormat = "General"
            Cells(Target.Row, 9).Value = "Figueira da Foz"
            
            Cells(Target.Row, 10).NumberFormat = "General"
            Cells(Target.Row, 10).Value = "Activa"
            
            Range("A" & Target.Row & ":J" & Target.Row).Interior.ColorIndex = 4
        End If
        If Target.Column = 10 Then
            If Target.Value = "Inactiva" Then
                Cells(Target.Row, 4).NumberFormat = "hh:mm"
                Cells(Target.Row, 4).Value = Now
                Range("A" & Target.Row & ":J" & Target.Row).Interior.ColorIndex = 3
            End If
            If Target.Value = "Activa" Then
                Cells(Target.Row, 4).Value = vbNullString
                Range("A" & Target.Row & ":J" & Target.Row).Interior.ColorIndex = 4
            End If
            If Target.Value = vbNullString Then
                Range("A" & Target.Row & ":J" & Target.Row).Interior.ColorIndex = 2
            End If
        End If
    End If
    
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.EnableEvents = True
    ActiveSheet.Protect
End Sub

This post has been edited by Miguel_A: Feb 12 2019, 04:41 AM

Attached File(s)

 Queimas_ing.zip ( 23.65K )Number of downloads: 2