CF to highlight any row in a column (2003 SP3)

[steveh]

Good morning

I am copying by code a data series from one sheet to another which then saves as ongoing histroical data. Only one of the cells (which is in column A) contains a date (one date followed by 30 Country / City names) and I want to use CF to highlight it to make it easier for when people look at the data. The date is formatted as 'Date'

I have Googled the question asking how to highlight a cell in CF which shows a date but most of them are about expiry dates and from and to dates. The nearest I could see was to use Cell Value is - Between =Today() - and = Today()+3650, I figured 10 years would be long enough but none of the cells with dates highlight.

Any thoughts please

[steveh]

I have maanaged to fix it by changing the from to today()-3650 and leaving the to as =Today()+3650 but for future reference is there something that would just identifify if any date was in a column, I thought of ISDATE (which does not exist) or something like that

[HansV]

There is no ISDATE worksheet function but we can make something similar ourselves. Copy the following function into a standard module in your workbook:

Code: Select all

Public Function IsADate(ByVal Arg) As Boolean
  On Error GoTo ExitHere
  IsADate = IsDate(Arg) And Arg > 0
ExitHere:
End Function

Select the cells you want to format. Let's say that A2 is the active cell within the selection.
Select Format | Conditional Formatting...
Select Formula Is from the first dropdown.
Enter the following formula in the box next to it:

=IsADate(A2)

Click Format etc.

[steveh]

There is no ISDATE worksheet function but we can make something similar ourselves. Copy the following function into a standard module in your workbook:

Code: Select all

Public Function IsADate(ByVal Arg) As Boolean
  On Error GoTo ExitHere
  IsADate = IsDate(Arg) And Arg > 0
ExitHere:
End Function

Select the cells you want to format. Let's say that A2 is the active cell within the selection.
Select Format | Conditional Formatting...
Select Formula Is from the first dropdown.
Enter the following formula in the box next to it:

=IsADate(A2)

Click Format etc.

Great, thanks Hans