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
CF to highlight any row in a column (2003 SP3)
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
CF to highlight any row in a column (2003 SP3)
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: CF to highlight any row in a column (2003 SP3)
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
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78789
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: CF to highlight any row in a column (2003 SP3)
There is no ISDATE worksheet function but we can make something similar ourselves. Copy the following function into a standard module in your workbook:
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.
Code: Select all
Public Function IsADate(ByVal Arg) As Boolean
On Error GoTo ExitHere
IsADate = IsDate(Arg) And Arg > 0
ExitHere:
End Function
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: CF to highlight any row in a column (2003 SP3)
Great, thanks HansHansV wrote:There is no ISDATE worksheet function but we can make something similar ourselves. Copy the following function into a standard module in your workbook:
Select the cells you want to format. Let's say that A2 is the active cell within the selection.Code: Select all
Public Function IsADate(ByVal Arg) As Boolean On Error GoTo ExitHere IsADate = IsDate(Arg) And Arg > 0 ExitHere: End Function
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.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin