Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to identify bad dates
I have a column of data, most of which are dates formatted a DD-MM-YYYY. However, some of the data are not formatted properly du to being incomplete. For example, instead of DD-MM-YYYY, the cel contains "June 2002." I would like a macro that identifies these "bad" dates and colors th cell red for every bad date found. Any help? Thanks again everybody -- Sandema ----------------------------------------------------------------------- Sandeman's Profile: http://www.excelforum.com/member.php...fo&userid=3244 View this thread: http://www.excelforum.com/showthread.php?threadid=52566 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to identify bad dates
Easier to do in Excel, I think. Insert an extra column and type:
=CELL("format",[cell ref]) and copy it down. It gives "D1" for the right format so you could then put in some conditional formatting to highlight any cells that don't equal "D1". "Sandeman" wrote: I have a column of data, most of which are dates formatted as DD-MM-YYYY. However, some of the data are not formatted properly due to being incomplete. For example, instead of DD-MM-YYYY, the cell contains "June 2002." I would like a macro that identifies these "bad" dates and colors the cell red for every bad date found. Any help? Thanks again everybody. -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=525667 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to identify bad dates
sub ColorColumns
with columns("F") .Interior.ColorIndex = xlnone On error Resume Next .Specialcells(xlconstants,xlTextValues).Interior.C olorIndex = 3 End With End Sub -- Regards, Tom Ogilvy "Sandeman" wrote: I have a column of data, most of which are dates formatted as DD-MM-YYYY. However, some of the data are not formatted properly due to being incomplete. For example, instead of DD-MM-YYYY, the cell contains "June 2002." I would like a macro that identifies these "bad" dates and colors the cell red for every bad date found. Any help? Thanks again everybody. -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=525667 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to identify bad dates
Superb Tom. Thanks -- Sandema ----------------------------------------------------------------------- Sandeman's Profile: http://www.excelforum.com/member.php...fo&userid=3244 View this thread: http://www.excelforum.com/showthread.php?threadid=52566 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to identify active worksheet in macro | Excel Worksheet Functions | |||
Identify a range of cells containing dates | Excel Worksheet Functions | |||
Macro to identify year within a date | Excel Programming | |||
formula to identify weekend dates | Excel Worksheet Functions | |||
Need a macro to Identify a users IP address | Excel Programming |