Hide Row Based on cell value which more than 45 days

[JayHo]

Hi all,
I was creating an excel to monitoring the maintenance progress. i would like to hide the row which based on the cell value (date) more than 45days.

i have limited knowledge on VBA here is my core.
i set the date on colum S, and would like to hide the row base on the value on colum . can you point out the erron on my code below?

Sub Hidedatemorethan45days()
'
'
'
Dim cell As Range
For Each cell In Range("S6:S67")
If cell.Value < "45" Then
cell.EntireRow.Hidden = False
End If
If cell.Value > "45" Then
cell.EntireRow.Hidden = True
End If
Next cell
End Sub


thanks in advance

 

[eiloken]

try:

Sub Hidedatemorethan45days()
    Dim cell As Range
    For Each cell In Range("S6:S67")
        If cell.Value <= 45 Then
            cell.EntireRow.Hidden = False
        Else
            cell.EntireRow.Hidden = True
        End If
    Next cell
End Sub

 

[JayHo]

try:

Sub Hidedatemorethan45days()
    Dim cell As Range
    For Each cell In Range("S6:S67")
        If cell.Value <= 45 Then
            cell.EntireRow.Hidden = False
        Else
            cell.EntireRow.Hidden = True
        End If
    Next cell
End Sub

It doesnt seem work porperly. As I ran the code, it hide all the row instead.
Is it possible because i set the colum S valum as Date and my valum for colum S are formular shown as below:

=IF($C$3="QC67",'Master records List'!BL6,IF($C$3="QC66",'Master records List'!BJ6,IF($C$3="QC65",'Master records List'!BH6,IF($C$3="QC64",'Master records List'!BF6,IF($C$3="QC63",'Master records List'!BD6,IF($C$3="QC62",'Master records List'!BB6,IF($C$3="QC61",'Master records List'!AZ6,"請輸入機號")))))))

 

[eiloken]

i cant see column S because your screenshot too blurry, so recap it to clearly

 

[JayHo]

i cant see column S because your screenshot too blurry, so recap it to clearly

Not sure why, i cant use the L2BB for capturing the mini excel but here are the screen shot for Colum S and it contained information from onther sheet as recall with formular below.

=IF($C$3="QC67",'Master records List'!BL6,IF($C$3="QC66",'Master records List'!BJ6,IF($C$3="QC65",'Master records List'!BH6,IF($C$3="QC64",'Master records List'!BF6,IF($C$3="QC63",'Master records List'!BD6,IF($C$3="QC62",'Master records List'!BB6,IF($C$3="QC61",'Master records List'!AZ6,"請輸入機號")))))))

Thanks.

 

[eiloken]

View attachment 111479

Not sure why, i cant use the L2BB for capturing the mini excel but here are the screen shot for Colum S and it contained information from onther sheet as recall with formular below.

=IF($C$3="QC67",'Master records List'!BL6,IF($C$3="QC66",'Master records List'!BJ6,IF($C$3="QC65",'Master records List'!BH6,IF($C$3="QC64",'Master records List'!BF6,IF($C$3="QC63",'Master records List'!BD6,IF($C$3="QC62",'Master records List'!BB6,IF($C$3="QC61",'Master records List'!AZ6,"請輸入機號")))))))

Thanks.

if that you want is hide all rows that have date greater than 45 days from today then try:

Sub Hidedatemorethan45days()
    Dim cell As Range
    For Each cell In Range("S6:S67")
        If cell.Value - Date >= 45 Then
            cell.EntireRow.Hidden = True
        Else
            cell.EntireRow.Hidden = False
        End If
    Next cell
End Sub

 

[JayHo]

Thanks for the assist, but the code is partly work for my excel only.
i suppose it only work for the date which less the today value.
For example, if the date is 26-Apr-2024 it work but 26-Oct-2024 and 26-Apr-2025 are not working. Do i need to set a value for today() as a reference for the code to run?
Also, when i run the code it show the window below:

 

[eiloken]

Thanks for the assist, but the code is partly work for my excel only.
i suppose it only work for the date which less the today value.
For example, if the date is 26-Apr-2024 it work but 26-Oct-2024 and 26-Apr-2025 are not working. Do i need to set a value for today() as a reference for the code to run?
Also, when i run the code it show the window below:

View attachment 111485

with this error, maybe date format in that cell is text, so vba can not get it value

 

[JayHo]

with this error, maybe date format in that cell is text, so vba can not get it value

For my refer colum, i set the format to date, but still get the same result.
my formular from refer colum are as below:

=IF(BK11="","Shall be inspected ASAP", EDATE(BK11, $AN11))

 

[eiloken]

For my refer colum, i set the format to date, but still get the same result.
my formular from refer colum are as below:

=IF(BK11="","Shall be inspected ASAP", EDATE(BK11, $AN11))

with "Type mismatch" error, that mean excel can not covert date data in cell to number to subtract for "today", you can try format data in column S as number and try again