Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change NumberFormat and Cell Fill Colour in SELECT CASE Construct
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change NumberFormat and Cell Fill Colour in SELECT CASE Construct
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change NumberFormat and Cell Fill Colour in SELECT CASE Constr
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I change the default fill colour in excel 2007 | Excel Discussion (Misc queries) | |||
how do I Change cell fill colour by placing letter in it | Excel Discussion (Misc queries) | |||
Can I use "or" in a select case construct? | Excel Programming | |||
Criteria - Automatic Change Font or Fill Colour | Excel Worksheet Functions | |||
Problem with Select Case construct? | Excel Programming |