none
How to compare data by the dates and alert me when the data difference is too big RRS feed

  • Question

  • Hi,

    I've wish to create a macro which could help analyse  the data by the dates and inform me via an alert/popup message. I have a forecast material planner document which i would update it daily and the data would change everyday. I have tons of data to look through and it take way too long if i were to do it manually. I wish to create a macro that could analyse through the data via the dates and alert me via popup message when the difference between the data is too big. Any clue or help will be much appreciated.

    EXAMPLE:
    01/01/2016                                             02/01/2016
    DATE                AMOUNT                           DATE                AMOUNT
    01/02/2016         5                                  01/02/2016         5                                 
    02/02/2016         5                                  02/02/2016         5                                  
    03/02/2016         6                                  03/02/2016         10                                  
    04/02/2016         5                                  04/02/2016         5                                  
    05/02/2016         10                                05/02/2016         20                                 
    06/02/2016         10                                06/02/2016         15                                 
    07/02/2016         5                                  07/02/2016         8                               
    08/02/2016         8                                  08/02/2016         9                                  
    09/02/2016         5                                  09/02/2016         10                                 
    10/02/2016         9                                  10/02/2016         2                                  


    • Edited by Relty Tuesday, November 29, 2016 6:27 AM
    Tuesday, November 29, 2016 1:51 AM

All replies

  • alert me via popup message when the difference between the data is too big.

    Makes no sense, you can do that with a simple formula and output a message or do a conditional formatting...

    Andreas.

    Tuesday, November 29, 2016 8:15 AM
  • Thanks for the reply. Based on your advised, I've went with conditional formatting but I would still wish to have a alert or popup message to notify me which cell is filled in red. So I found a code which is supposedly to work, but when I tried to run it nothing happen.

    CODE

    Sub Button5_Click()

    Dim ws As Worksheet
    Dim i As Integer
    Set ws = Sheets("COMPARISON")
    i = 1
    Do Until i = 300
        If ws.Range("F" & i).Interior.Color = RGB(255, 0, 0) Then
            MsgBox "F" & i & "  is red!!"
        End If
    i = i + 1
    Loop

    End Sub

    Wednesday, November 30, 2016 10:25 AM
  • So I found a code which is supposedly to work, but when I tried to run it nothing happen.

    You can't get the color of a conditional formatting from a cell. To get it is much more complicated and a lot of work.

    A very simple solution is to use the condition itself in a formula, use one cell at the top of the sheet and display an alert.

    Andreas.

    Wednesday, November 30, 2016 11:08 AM
  • Ah, I've actually found a code that could get the color from a conditional formatting from a cell. However, the popup message will only display the column number and cell number. How do I go about converting from displaying column number to column letter ?

    CODE

    Sub myCFtest()

    Dim RowNumber As Long
    Dim ColumnNumber As Long
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "F").End(xlUp).Row
    Dim LastColumn As Long
    LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim FindInteriorColor As Long

    For RowNumber = 3 To LastRow
    For ColumnNumber = 6 To LastColumn

    FindInteriorColor = 255
      
      If Cells(RowNumber, ColumnNumber).DisplayFormat.Interior.Color = FindInteriorColor Then
        MsgBox ("Data Changed at " & ColumnNumber & " " & RowNumber)
      End If
      
      Next ColumnNumber
      Next RowNumber

    End Sub

    • Edited by Relty Thursday, December 1, 2016 3:39 AM
    Thursday, December 1, 2016 2:06 AM
  • the popup message will only display the column number and cell number. How do I go about converting from displaying column number to column letter ?

    Use the Address property.

    MsgBox Cells(RowNumber, ColumnNumber).Address(0,0)

    Andreas.

    Thursday, December 1, 2016 8:07 AM
  • Hi,

    It seems that your issue has been resolved, I suggest you mark helpful post as answer or you could share your solution with us, so that it would help others if they have similar issue.

    If not, please let us know.

    If you have any other new issues, please feel free to post new thread.

    Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 7, 2016 2:37 AM
    Moderator