Thanks, I think I'm geting somewhere now... The only reason I used the case
construct was because I'm already using to for a lot of other 'non-date' type
text fields.
I've tweaked your code a bit after some more research, as it kept falling
over at Cell.Value < TODAY(), i've used DateTime.Date instead (see below).
It's starting to format the cells correctly, however it starts chewing up
memory as it preocesses teh date cells. For some reason it seems to be
switching the dd and mm values in the cell, for example, 30/04/2008 (30th
April) becomes 04/30/2008, which presumably upsets excel if it's now
expecting dd/mm/yyyy! Really don't know where to go with this... there must
be an easier way??
If Cell.Value Like "##/##/####" Then
Cell.NumberFormat = "dd/mm/yyyy"
Cell.Value = Cell.Value
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = True
If Cell.Value < DateTime.Date Then
Cell.Interior.ColorIndex = 4 'Green
End If
End If
"Rick Rothstein (MVP - VB)" wrote:
> First, you don't need a Select Case construction for what you are doing... a
> simple If-Then will suffice. Second, try setting the Value property equal to
> itself (after the NumberFormat line) in order to reset the value. I'm
> thinking something like this should work...
>
> For Each Cell In Rng1
> If Cell.Value Like "##/##/####" Then
> Cell.NumberFormat = "dd/mm/yyyy"
> Cell.Value = Cell.Value
> Cell.Interior.ColorIndex = xlNone
> Cell.Font.Bold = False
> If Cell.Value < TODAY() Then
> Cell.Interior.ColorIndex = 4 'Green
> End If
> End If
> Next
>
> Rick
>
>
> "rmellison" <(E-Mail Removed)> wrote in message
> news:11BA826F-E899-49E4-9723-(E-Mail Removed)...
> > Hi,
> >
> > I'm using the Select Case construct for conditional formatting on my
> > spreadsheet. The data is live linked to a SharePoint table, so when
> > updated,
> > is pulled through in Text format. Within this data there may be some
> > dates,
> > which are shown as Text in the format "dd/mm/yyyy" when pulled through. I
> > need to turn these into Excel dates (ie 39652, formatted as 'Date') and
> > format these cells according to whether or not they are prior to TODAY().
> >
> > I'm not a VB coder - most of the Case constructs have been adapted from
> > online example. Here's what i've written for this particular Case:
> >
> > For Each Cell In Rng1
> > Select Case Cell.Value
> > Case "##/##/####"
> > Cell.NumberFormat = "dd/mm/yyyy"
> > Cell.Interior.ColorIndex = xlNone
> > Cell.Font.Bold = False
> > If Cell.Value < TODAY() Then
> > Cell.Interior.ColorIndex = 4 'Green
> > End If
> > End Select
> > Next
> >
> > I think there are several things that could be wrong here, mainly
> > '##/##/####' and Cell.Value < TODAY(), but no idea how to correct. Any
> > advice, or an easier way to do this automatically would be appreciated.
>
>
|
Similar Threads
