Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a report that has a Date Stamp column. I would like to be prompted for a date and then conditionally format the rows (with color) where the date (in the Date Stamp column) is greater than the prompted date. Is this possible. If a prompt is not possible, then just how to conditionally format according to a date. Thanks for your help.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please see attached. The sheet contains a button that fires the following code:

    Code:
    Sub DatePlease_Click()
    	Dim dDate As Date
    	dDate = Application.InputBox("Date Please", "Date Please", Date)
    	ActiveWorkbook.Names.Add Name:="DateCheck", RefersTo:=dDate
    End Sub
    The code assigns the entered date to a named range. This name range is then used in the conditional formatting of the cells. In this case it is:=D2>DateCheck, where D2 is the cell being compared to the entered date.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry - the attachment "fell off"
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='764812' date='11-Mar-2009 15:14']Sorry - the attachment "fell off"[/quote]


    I am going to forgo the Prompt. What I am trying to do is name the Date Stamp column a named range - say DateStmp. Then I want to highlight the entire spreadsheet. Go to conditional format and say Formula is - =DateStmp > 2/1/09. I want the rows to turn yellow where the dates in the Date Stamp column are greater than 2/1/09. I did this but the whole spreadsheet is highlighted. What am I doing wrong? Is there a certain syntax for entering the date?

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='LindaR' post='764824' date='11-Mar-2009 11:08']What I am trying to do is name the Date Stamp column a named range - say DateStmp. Then I want to highlight the entire spreadsheet. Go to conditional format and say Formula is - =DateStmp > 2/1/09. I want the rows to turn yellow where the dates in the Date Stamp column are greater than 2/1/09. I did this but the whole spreadsheet is highlighted. What am I doing wrong? Is there a certain syntax for entering the date?[/quote]

    Using your current method, you are comparing an entire named range against a value. The value being evaluated is the first value in the range. So if the first value in DateStmp is greater than the date, then all the values in DateStmp are flagged as True.

    Yes, there is certain syntax necessary for entering a date. The value you are comparing is actually 0.2222 (or 2 divided by 1 divided by 9) which would be 5:20 am on Dec 31,1899 (displayed in Excel as Jan 0, 1900)
    One syntax method would be Date(yyyy,mm,dd) or Date(2009,2,1) for your example. Another would be =Datevalue("2/1/2009"). I think there's another way but cannot think of it right now.

    You can use the method I demonstrated in my first post, by entering the date as a named range and then comparing the date cells against that named range. When you enter the date into the Refers to box, Excel will convert it to it's numeric equivalent. Or you could enter it as Date(2009,02,01)

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='764833' date='11-Mar-2009 16:34']Using your current method, you are comparing an entire named range against a value. The value being evaluated is the first value in the range. So if the first value in DateStmp is greater than the date, then all the values in DateStmp are flagged as True.

    Yes, there is certain syntax necessary for entering a date. The value you are comparing is actually 0.2222 (or 2 divided by 1 divided by 9) which would be 5:20 am on Dec 31,1899 (displayed in Excel as Jan 0, 1900)
    One syntax method would be Date(yyyy,mm,dd) or Date(2009,2,1) for your example. Another would be =Datevalue("2/1/2009"). I think there's another way but cannot think of it right now.

    You can use the method I demonstrated in my first post, by entering the date as a named range and then comparing the date cells against that named range. When you enter the date into the Refers to box, Excel will convert it to it's numeric equivalent. Or you could enter it as Date(2009,02,01)[/quote]


    How do I do this without the prompt. Since this will be done on a different spreadsheet each time, I just want something simple - if that is possible.

    I named the range for the Date Stamp column - Datestmp. Now where do I go from here. I am not quite sure what you mean by the refers to box - is that in the conditional format?

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='LindaR' post='764842' date='11-Mar-2009 23:55']How do I do this without the prompt. Since this will be done on a different spreadsheet each time, I just want something simple - if that is possible.

    I named the range for the Date Stamp column - Datestmp. Now where do I go from here. I am not quite sure what you mean by the refers to box - is that in the conditional format?[/quote]


    If your date are in a col, you can select the whole column for the CF formula
    see the attached, adjust the range to suit yours
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using the method the franciz and I are demonstrating, there is no need to assign the DateStmp name to the range. Both method compare a specific cell to a value.


    Attached version has been modified to remove the prompt.
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='franciz' post='764854' date='11-Mar-2009 16:21']If your date are in a col, you can select the whole column for the CF formula
    see the attached, adjust the range to suit yours[/quote]

    That seems simple enough. Is there a way to highlight the whole row?

  10. #10
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='LindaR' post='764865' date='11-Mar-2009 12:48']That seems simple enough. Is there a way to highlight the whole row?[/quote]

    Highlight your row(s).
    For the condition formatting use:
    =$A2 >= date(2009,2,1)

    Where A is the column that contains the date and 2 is the row currently selected - look in the "Name Box" (the little box to the left of the formula bar) to find the row number.

  11. #11
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='LindaR' post='764865' date='12-Mar-2009 00:48']That seems simple enough. Is there a way to highlight the whole row?[/quote]

    same method, select the whole row instead before apply CF formula

    or select any cells including multiple cells by pressing Ctrl and use your mouse to select those
    cells you want to have CF apply.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='764868' date='11-Mar-2009 17:55']Highlight your row(s).
    For the condition formatting use:
    =$A2 >= date(2009,2,1)

    Where A is the column that contains the date and 2 is the row currently selected - look in the "Name Box" (the little box to the left of the formula bar) to find the row number.[/quote]


    Thank you for your patience and help. It worked perfectly......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •