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. |
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 |