find cells with dates using vba macro (beginner)

[alksndr]

Hi Gurus,

I want to find the cells with dates using vba macro.

I'm hoping to find dates under column Q which contains dates formatted as "m/d/yyyy hh:mm" and a text "---". After that, the return value is in column B which I plan to use offset function.


I just also want to add that I'm a beginner in vba macro so any simple vba language will very helpful to me.


Thanks in advance!

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I am not quite sure what your data looks like. Is it a real Excel Date field or something else. By that I mean does the cell contain a number representing the number of days since 1/1/1900 and a fractional part representing the fraction of the day. I ask because I am slightly worried about the ' and a text "---" ' part of your question.

If you just want to find a date/time then look at the standard Find/Replace options on the Editing tab. If you select the Options button in there you can search on Formats on the next screen.
If you can make that work then you could record it with the Macro Recorder.

If you can provide an example of what your data is actually like then I can try something out here.

Regards,

 

[alksndr]

Hi RickXL,

after some time, I actually got this solved. I just used For i function to find those cells <> "---" and it works perfectly.

Here's how it works for me. Thank you for your reply!

Sub passed()

Application.ScreenUpdating = False

For i = 2 To 20000

Cells(i, 17).Activate

If ActiveCell.Value <> "---" Then
    ActiveCell.Offset(0, -15).Activate
    ActiveCell.Font.ColorIndex = 3
    
End If

Next i

Application.ScreenUpdating = True

End Sub

 

[RickXL]

OK, I think this might be quicker, though.

Sub passed()

    Application.ScreenUpdating = False
    
    For i = 2 To 20000
        If Cells(i, 17) <> "---" Then Cells(i, 17).Offset(0, -15).Font.ColorIndex = 3
    Next i
    
    Application.ScreenUpdating = True

End Sub