+ Reply to Thread
Results 1 to 3 of 3

Slight modification to formatting sub-routine

Hybrid View

pglufkin Slight modification to... 11-07-2013, 02:44 PM
6StringJazzer Re: Slight modification to... 11-07-2013, 07:02 PM
Solus Rankin Re: Slight modification to... 11-07-2013, 07:06 PM
  1. #1
    Registered User
    Join Date
    06-19-2005
    Posts
    49

    Slight modification to formatting sub-routine

    I have a small sub routine below I would like to make a slight modification to. The routine currently references formatting relative to the Offset statement in bold below. Instead, I would like the formatting to come from a cell address listed in a cell just to the left of the cursor when the statement is encountered. Can anyone help? Thank you.

    Sub ()
    
    Dim lR As Long, col  As Range, c As Range
    lR = Cells(Rows.Count, Selection.Column).End(xlUp).Row
    Application.ScreenUpdating = False
    For Each c In Range(Cells(Selection.Row, Selection.Column), Cells(lR, Selection.Column))
        If Not IsEmpty(c) Then
            On Error Resume Next
            Set col = Offset(0, -c.Offset(0, 1).Value) '(WANT TO CHANGE TO THE CELL ADDRESS WHICH WILL BE FOUND IN THE CELL TO THE LEFT OF THE CURSOR WHEN THIS STATEMENT IS ENCOUNTERED)
            col.Copy
            c.PasteSpecial xlPasteFormats
         End If
    Next c
    Calculate
    
    End Sub
    Moderator's Note: Moved from General forum --6SJ
    Last edited by 6StringJazzer; 11-07-2013 at 06:54 PM. Reason: Forum change

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,758

    Re: Slight modification to formatting sub-routine

    I think this will do it:

            Set col = Range(ActiveCell.Offset(0, -1).Value)
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Slight modification to formatting sub-routine

    You might encounter an error if the cursor is in column A (you can't offset to the left of column A). And with your on error resume next statement it won't be immediately apparent.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

+ 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. [SOLVED] Slight Modification Request VBA Code - Text File to Excel
    By spiwere in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 03-22-2013, 04:39 AM
  2. [SOLVED] Need slight modification to VBA row-merging code
    By brandonlaw in forum Excel General
    Replies: 2
    Last Post: 06-08-2012, 10:22 AM
  3. Slight modification to copy paste loop
    By rhudgins in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-19-2010, 04:09 PM
  4. [SOLVED] Need a slight modification to code
    By Dean in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2006, 11:35 AM
  5. Slight problem - routine to add quotes to a column
    By throat.wobbler.mangrove@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-15-2005, 11:05 AM

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