+ Reply to Thread
Results 1 to 4 of 4

Using 'Do Until' within "Workbook_Open".

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-08-2012
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2016
    Posts
    142

    Using 'Do Until' within "Workbook_Open".

    What I am trying to do is on spreadsheet open is;

    Checks cell E2 (contains a date)
    - If E2 < second date (specified on different sheet within same workbook) perform action
    - Move to next row, same column
    ...and this will loop until E2 >= the second date

    I think I am getting close (don't laugh if I am way off) but just can't get it working correctly.
    The code I have is....

    Private Sub Workbook_Open()
        Dim TestDate As String
        TestDate = ""
        Dim i As Integer
        i = 2
        Dim wks As Worksheet
            With Worksheets("Block")
                    Do Until TestDate = "End"
                        If Range(Cells(i, 5)) < Worksheets("Validation").Range("$A$21") Then
                            Range("A" & ActiveCell.Row).Resize(, 13).Interior.ColorIndex = xlColorIndexNone
                            i = i + 1
                        Else
                            TestDate = "End"
                        End If
                    Loop

    Thank you.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Using 'Do Until' within "Workbook_Open".

    If Range(Cells(i, 5)) < Worksheets("Validation").Range("$A$21") Then
    oughta be
    If .Cells(i, 5).Value < Worksheets("Validation").Range("$A$21").value Then
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Contributor
    Join Date
    11-08-2012
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2016
    Posts
    142

    Re: Using 'Do Until' within "Workbook_Open".

    That's great, silly error. Thanks for your help

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Using 'Do Until' within "Workbook_Open".

    you're welcome :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Preventing "Workbook_Open" running when a file is opened from email
    By jshaw82 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-15-2013, 07:42 AM
  2. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  3. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  4. why does "Private Sub Workbook_open" not work?
    By stin25 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-10-2009, 04:29 PM
  5. Replies: 7
    Last Post: 05-13-2006, 05:02 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1