Colour a row if a date is entered 1

[JohnAcc]

Hi All,

I have the following problem. I need to highlight the entire row a different colour if a date is entered in any cell in column D.

I was thinking of using the Selection Change Event but I have no way of knowing the previous cell reference.

Any ideas ?

Rgds, John

 

[PHV]

And what about a conditional format ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

Hmm - not sure how you would test for a date in conditional format as the underlying data is merely a double data type

Have a look at the worksheet_CHANGE event rather than the SELECTION CHANGE event

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[PHV]

xlbo, what about a public custom function playing with IsDate ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zathras]

As Geoff stated, your best bet would probably be to use VBA and process the Worksheet_Change event.

If you don't want to (or can't) use VBA, you could use a hidden column to hold a 1 or a 0 according to whether the corresponding cell from column D is a date, assign a cell to hold a SUM function, then use conditional formatting to control the coloring based on whether or not the SUM is zero or greater than zero.

A formula you could use is something like this:

=IF(OR(ISERROR(DATEVALUE(TEXT(D2,"mm/dd/yy"))),D2<37987,D2>38717),0,1)

The constants 37987 and 38717 represent the dates 1/1/2004 and 12/31/2005 respectively. This is to avoid treating ordinary numbers as dates since the DATEVALUE function does not care. Adjust the numbers to suit the date range you are working with.

The conditional format formula would be something like this:

=$F$1>0

where F1 contains the SUM formula. Select the entire row by clicking on the row number, and then choose Format/Conditional Formatting... from the menu. Change the condition from "Cell Value Is" to "Formula Is"

 

[xlbo]

PHV - yup - that'd do it but wasn't sure whether JohnAcc would realise that

Zathras - nice solution (as ever) but I reckon that VBA is acceptable as we are in the VBA forum !!

JohnAcc - here is a code sample for changing the colour of the row but you need to think about what happens to the other rows when this one changes - do all other rows get reset or should rows with dates still be shown as a different colour ?? If the latter, what happens when a date is deleted ??

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
If IsDate([Target]) Then Rows(Target.Row).Interior.ColorIndex = 3
End Sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[JohnAcc]

Cheers Xlbo