Thanks, that does the trick. Is there a way to do it without the loop? It's a
bit slow when there is a lot of data....

"Gary''s Student" wrote:

Select the cells and run:

Sub ClickMe()
For Each r In Selection
r.Select
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents
Next
End Sub

This basically re-asserts the cells
THEN
apply the formatting.
--
Gary''s Student - gsnu200841


"nick" wrote:

Hi,

I have some cells with the following formatted dates loaded from XML, they
come in as text formatted "01AUG2008"

I used the following VBA to change the cell storage format - date
Selection.NumberFormatLocal = "[$-409]dd-mmm-yy;@"

Then I have a sumproduct formula that uses date ranges as one of it's
parameters. It doesn't pick up the cell data until I click in it and hit
enter once. Then it can be referenced. I took a macro of that action
"clicking and hit enter" but it looks to be setting a formula for each cell
specific to it's contents
ActiveCell.FormulaR1C1 = "18-Apr-2008"

What I want is the cell to be formatted as a date and without having to
click into it. Does anyone know how I can do this?

Thanks in advance,

Nick