+ Reply to Thread
Results 1 to 8 of 8

Merge 2 VBA into 1

Hybrid View

  1. #1
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    MS365
    Posts
    241

    Merge 2 VBA into 1

    hello Forum... i know very little of VBA... i do have these 2 codes in which i need in 1... any help is greatly appreciated

    Sub ClockIn()
    
    If Not IsClockedOut([employee]) Then
        MsgBox "You must resolve the current issue first."
        Exit Sub
    End If
    
    With clockdata.Range("A" & Rows.Count).End(xlUp)
        .Offset(1, 0) = [employee]
        .Offset(1, 1) = [Desc]
        .Offset(1, 2) = [ID]
        .Offset(1, 3) = Now
    End With
    
    End Sub

    Sub red()
    '
    ' red Macro
    '
    
    '
        Range("B16:C16").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("A9:A16").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("D9:D16").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("A8:D8").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("A1").Select
    End Sub

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Merge 2 VBA into 1

    Hello, can you try this:

    Sub ClockIn()
    
    If Not IsClockedOut([employee]) Then
        MsgBox "You must resolve the current issue first."
        Exit Sub
    End If
    
    With clockdata.Range("A" & Rows.Count).End(xlUp)
        .Offset(1, 0) = [employee]
        .Offset(1, 1) = [Desc]
        .Offset(1, 2) = [ID]
        .Offset(1, 3) = Now
    End With
    
    With clockdata
        .Range("B16:C16").Interior.Color = 255
        .Range("A9:A16").Interior.Color = 255
        .Range("D9:D16").Interior.Color = 255
        .Range("A8:D8").Interior.Color = 255
    End With
    
    End Sub
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    MS365
    Posts
    241

    Re: Merge 2 VBA into 1

    welll.... it did work, but it highlighted the cells on sheet 2 which is titled "Log"
    Can we highlight sheet 1 titled "Incident"

  4. #4
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Merge 2 VBA into 1

    It wasn't clear....

    Simply change this part:

    With Sheets("Incident")
        .Range("B16:C16").Interior.Color = 255
        .Range("A9:A16").Interior.Color = 255
        .Range("D9:D16").Interior.Color = 255
        .Range("A8:D8").Interior.Color = 255
    End With

  5. #5
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    MS365
    Posts
    241

    Re: Merge 2 VBA into 1

    Perfect TY so much GC Excel!

    I have 1 more issue i need help with (not sure if a new thread would be appropriate since it is similar)

    I would like this piece of code to be attached to a 2nd button and the red cells go back to normal.


    Sub ClockOut()
    
    On Error GoTo NOTONLIST
    With clockdata.Columns(1).Cells.Find([employee], Range("A" & Rows.Count).End(xlUp).Offset(1, 0), , xlWhole, , xlPrevious).Offset(0, 4)
        If .Value <> "" Then
            MsgBox "You must have an active issue before applying resolution."
            Exit Sub
        End If
        .Value = Now
    End With
    Exit Sub
    
    NOTONLIST:
            MsgBox "You must have an active issue before applying resolution."
            Exit Sub
    
    End Sub

  6. #6
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    MS365
    Posts
    241

    Re: Merge 2 VBA into 1

    so as to remove the red cells... we could overwrite with white or remove fill... whichever is easiest

  7. #7
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Merge 2 VBA into 1

    To remove fill on a range, you can use this:

    Sub test()
       With Sheets("Incidents")
          .Range("A8:D16").Interior.Color = xlNone
       End With
    End Sub

  8. #8
    Forum Contributor keith740's Avatar
    Join Date
    04-24-2015
    Location
    Racine, Ohio
    MS-Off Ver
    MS365
    Posts
    241

    Re: Merge 2 VBA into 1

    perfect... TY guys!

+ 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. Replies: 0
    Last Post: 06-24-2015, 02:34 AM
  2. Email merge from excel merge file using pre-saved word template
    By d_max_c in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2014, 12:33 PM
  3. Replies: 2
    Last Post: 01-22-2013, 11:37 AM
  4. Email Merge with variable merge records break by client level
    By sss047 in forum Word Formatting & General
    Replies: 0
    Last Post: 04-15-2011, 04:25 AM
  5. Merge 2 Worksheets (Same concept as RegEdit Merge or Outlook Vcard)
    By stevedes7 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-20-2009, 11:17 PM
  6. Replies: 0
    Last Post: 04-21-2006, 03:40 PM
  7. merge with Word How can I get excel to mail merge zip codes plus 4 correctly?
    By Kathy at Sauder Feeds in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-30-2005, 07: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