Selelcting a cell depending on contents

[BenyG]

This has been driving me crazy, from initially realising that code generated by a macro recorder (Ctrl+F) does not work to using this.

Essentially I want to create a 'Go to Today' function which will highlight the cell where the date is the same as todays. The code below does not work but I cannot see why. I have even ensursed that it searches for the exact correct text by ensuring the same format is being used.

Range A8:A911 contains all the dates and I need it to select today's date.

Can anyone help?

Dim todaycropped
todaycropped = Format(now, "dd/mm/yyyy&quot

Range("A8:A911&quot.Select
For Each Cl In Selection
If Cl.Value = todaycropped Then
Cl.Select
End If
Next

Thanks for any help,

BenyG

 

[waynerenaud]

This worked for me. Add as date to your dim statement.

Dim todaycropped As Date
todaycropped = Format(now, "dd/mm/yyyy&quot

Range("A8:A911&quot.Select
For Each Cl In Selection
If Cl.Value = todaycropped Then
Cl.Select
End If
Next

 

[TonyJollans]

Hi BenyG,

Your problem is probably caused by the formatting. Todaycropped is coerced to a string by the Format function and it does not then ever match the Date in the cells because the types are different. Try using If Cl.Value = Date Then in your loop instead (and discarding your todaycropped variable).

Enjoy,
Tony

 

[BenyG]

The code works!

Thanks very much for your help.