Change NumberFormat and Cell Fill Colour in SELECT CASE Construct

[rmellison]

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.

 

[Rick Rothstein \(MVP - VB\)]

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]

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