Results 1 to 15 of 15

Check Column A for date before running macro

Threaded View

  1. #1
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Check Column A for date before running macro

    Hello, I have some code here that I am using to find matches in Column D and then remove colored cell once the match is found. I am in putting the data line by line from a Barcode scanner and only want to run the macro if the date in Column A is today. Here is the code I am using.

     
    Sub Highlight_Duplicate_Entry()
     
    
        Dim ws As Worksheet
        Dim cell As Range
        Dim myrng As Range
        Dim myrngDate As Range
        Dim clr As Long
        Dim lastCell As Range
    
        Set ws = ThisWorkbook.Sheets("Sheet2")
        Set myrng = ws.Range("D1:D" & Range("D" & ws.Rows.Count).End(xlUp).Row)
        Set myrngDate = ws.Range("A1:A" & Range("A" & ws.Rows.Count).End(xlUp).Row)
    
        With myrng
            Set lastCell = .Cells(.Cells.Count)
        End With
    
        myrng.Interior.ColorIndex = xlNone
    
        clr = 3
    
        For Each cell In myrng
    
            If Application.WorksheetFunction.CountIf(myrng, cell) = 1 Then
                ' addresses will match for first instance of value in range
                If myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastCell).Address = cell.Address Then
                    ' set the color for this value (will be used throughout the range)
                    cell.Interior.ColorIndex = clr
                    clr = clr + 1
                Else
                    ' if not the first instance, set color to match the first instance
                    cell.Interior.ColorIndex = myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastCell).Interior.ColorIndex
                End If
            End If
    
        Next
    
    End Sub
    Last edited by 6StringJazzer; 02-07-2014 at 05:20 PM. Reason: fixed code formatting

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to over ride compatiblity check message when running a macro
    By sakthisan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2014, 05:51 PM
  2. Replies: 7
    Last Post: 10-04-2012, 01:32 PM
  3. [SOLVED] Check Criteria On Button Click Before Running Macro
    By swordswinger710 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 06-06-2012, 04:56 PM
  4. VBA Code to check cell before running macro
    By kolovel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-17-2010, 06:19 PM
  5. [SOLVED] How to check a cell for content before running macro.
    By Incoherent in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2005, 12:05 PM

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