+ Reply to Thread
Results 1 to 5 of 5

Select alternative columns as range

Hybrid View

  1. #1
    Registered User
    Join Date
    07-21-2015
    Location
    Orlando, FL
    MS-Off Ver
    2016
    Posts
    19

    Select alternative columns as range

    Hi, I am trying to use this code I googled to get a time stamp populated. The change I need to make is to select rows 10:50 of every alternative column as the range (instead of the A2:A10).

     Private Sub Worksheet_Change(ByVal Target As Excel.Range)
            With Target
                If .Count > 1 Then Exit Sub
                If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
                    Application.EnableEvents = False
                    With .Offset(0, 1)
                        .NumberFormat = "dd mmm yyyy"
                        .Value = Date
                    End With
                    Application.EnableEvents = True
                End If
            End With
        End Sub

    Thanks for your time.
    Last edited by Cobelatte; 12-12-2016 at 08:02 PM. Reason: Edited as requested by Mod

  2. #2
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Select alternative columns as range

    Try this:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        With Target
            If .Count > 1 Then Exit Sub
            If .Row >= 10 And .Row <= 50 And .Column Mod 2 = 1 Then
                Application.EnableEvents = False
                With .Offset(0, 1)
                    .NumberFormat = "dd mmm yyyy"
                    .Value = Date
                End With
                Application.EnableEvents = True
            End If
        End With
    End Sub
    Regards,
    Stephen

    If you feel someone has helped you please thank them and click on the star on their post to add reputation.
    Please ensure that you use code tags where relevant, and mark solved threads as solved.
    Most of all please be respectful and understanding of others.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,937

    Re: Select alternative columns as range

    code tags updated.
    Last edited by protonLeah; 12-12-2016 at 08:05 PM.
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    07-21-2015
    Location
    Orlando, FL
    MS-Off Ver
    2016
    Posts
    19

    Re: Select alternative columns as range

    Sorry about that. I have fixed it now.

  5. #5
    Registered User
    Join Date
    07-21-2015
    Location
    Orlando, FL
    MS-Off Ver
    2016
    Posts
    19

    Re: Select alternative columns as range

    Stephen, Thanks for the code. It works perfectly.

    Pete
    Last edited by Cobelatte; 12-12-2016 at 11:06 PM.

+ 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. How do I select a range of columns using Union?
    By c.davidson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-17-2016, 04:52 AM
  2. [SOLVED] Select text from a range of columns
    By robertwood0015 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-03-2016, 07:08 AM
  3. How to select Multiple columns in a range?
    By kavitha.v in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-12-2014, 08:45 AM
  4. alternative to .Select?
    By b1ll in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2008, 05:38 PM
  5. Modfiy row of VB to select range of columns
    By Darin Kramer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2006, 05:55 PM
  6. Select updated data from a range of columns
    By Alylia in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. Select updated data from a range of columns
    By Alylia in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 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