llldebaserll
10-03-2013, 02:33 PM
Hi,
I'm having one heck of a time figuring out where I've goofed up my coding.
I'm am importing data from an online database each morning and want to run a macro that will detect any changes from the day before (new worksheet each day).
If something has changed, I want to simply highlight the row so I can find it amongst the average 4000 entries.
The problem I am running into is that it doesn't seem to be finding/marking the correct rows. On my actual worksheet, it seemed to highlight EVERY row, even if something hadn't changed. And now, on the mock-up version I made to post here, it seems to be only highlighting the first 15 rows, then stopping. BUT the two worksheets are completely identical. I literally just copied one and renamed it.
I hope this makes sense. Here is the Sub I'm working with. (I'm sure it's a bit primitive, I'm rather new to vba)
Sub A_CompareAccounts3()
Dim OrigOffset As Integer
Dim InvNo As String
Dim Disp1 As String
Dim Disp2 As String
Dim ItemAmt As String
Dim ColorTag As Boolean
Dim ColorTag2 As Boolean
Dim Counter As Long
Dim PctDone As Single
Dim TotalRows As Long
' Progress Bar Set Up
Set ProgressIndicator = New UserForm1
ProgressIndicator.Show vbModeless
If TypeName(ActiveSheet) <> "Worksheet" Then
Unload ProgressIndicator
Exit Sub
End If
' Set Progress Variables
TotalRows = 1
Counter = 1
Range("A5").Select
Do Until IsEmpty(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
TotalRows = TotalRows + 1
Loop
' Starting Point
Range("D5").Select
OrigOffset = 0
' Get Invoice Number
Do Until IsEmpty(ActiveCell.Value)
InvNo = ActiveCell.Value
ColorTag = False
ColorTag2 = False
Disp1 = ActiveCell.Offset(0, 5).Value
Disp2 = ActiveCell.Offset(0, 6).Value
ItemAmt = ActiveCell.Offset(0, 2).Value
Counter = Counter + 1
' Find Invoice Number
Sheets(2).Activate
Range("D5").Select
Do Until IsEmpty(ActiveCell) Or ActiveCell.Value = InvNo
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 0).Select
' Compare Data
If ActiveCell.Offset(0, 5).Value <> Disp1 Then ColorTag = True
If ActiveCell.Offset(0, 6).Value <> Disp2 Then ColorTag = True
If ActiveCell.Offset(0, 2).Value <> ItemAmt Then ColorTag = True
' Tag Different
Sheets(1).Activate
Range("D5").Select
ActiveCell.Offset(OrigOffset, 0).Select
If ColorTag = True Then Call ColorWholeRow
' Update Progress Bar
PctDone = Counter / TotalRows
Call UpdateProgress(PctDone)
' Move to Next Invoice Number
OrigOffset = OrigOffset + 1
ActiveCell.Offset(OrigOffset, 0).Select
Loop
' Unload Progress Bar
Unload ProgressIndicator
Set ProgressIndicator = Nothing
End Sub
Here is a mock-up of the workbook
10663
Any help would be SUPER appreciated.
I'm having one heck of a time figuring out where I've goofed up my coding.
I'm am importing data from an online database each morning and want to run a macro that will detect any changes from the day before (new worksheet each day).
If something has changed, I want to simply highlight the row so I can find it amongst the average 4000 entries.
The problem I am running into is that it doesn't seem to be finding/marking the correct rows. On my actual worksheet, it seemed to highlight EVERY row, even if something hadn't changed. And now, on the mock-up version I made to post here, it seems to be only highlighting the first 15 rows, then stopping. BUT the two worksheets are completely identical. I literally just copied one and renamed it.
I hope this makes sense. Here is the Sub I'm working with. (I'm sure it's a bit primitive, I'm rather new to vba)
Sub A_CompareAccounts3()
Dim OrigOffset As Integer
Dim InvNo As String
Dim Disp1 As String
Dim Disp2 As String
Dim ItemAmt As String
Dim ColorTag As Boolean
Dim ColorTag2 As Boolean
Dim Counter As Long
Dim PctDone As Single
Dim TotalRows As Long
' Progress Bar Set Up
Set ProgressIndicator = New UserForm1
ProgressIndicator.Show vbModeless
If TypeName(ActiveSheet) <> "Worksheet" Then
Unload ProgressIndicator
Exit Sub
End If
' Set Progress Variables
TotalRows = 1
Counter = 1
Range("A5").Select
Do Until IsEmpty(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
TotalRows = TotalRows + 1
Loop
' Starting Point
Range("D5").Select
OrigOffset = 0
' Get Invoice Number
Do Until IsEmpty(ActiveCell.Value)
InvNo = ActiveCell.Value
ColorTag = False
ColorTag2 = False
Disp1 = ActiveCell.Offset(0, 5).Value
Disp2 = ActiveCell.Offset(0, 6).Value
ItemAmt = ActiveCell.Offset(0, 2).Value
Counter = Counter + 1
' Find Invoice Number
Sheets(2).Activate
Range("D5").Select
Do Until IsEmpty(ActiveCell) Or ActiveCell.Value = InvNo
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 0).Select
' Compare Data
If ActiveCell.Offset(0, 5).Value <> Disp1 Then ColorTag = True
If ActiveCell.Offset(0, 6).Value <> Disp2 Then ColorTag = True
If ActiveCell.Offset(0, 2).Value <> ItemAmt Then ColorTag = True
' Tag Different
Sheets(1).Activate
Range("D5").Select
ActiveCell.Offset(OrigOffset, 0).Select
If ColorTag = True Then Call ColorWholeRow
' Update Progress Bar
PctDone = Counter / TotalRows
Call UpdateProgress(PctDone)
' Move to Next Invoice Number
OrigOffset = OrigOffset + 1
ActiveCell.Offset(OrigOffset, 0).Select
Loop
' Unload Progress Bar
Unload ProgressIndicator
Set ProgressIndicator = Nothing
End Sub
Here is a mock-up of the workbook
10663
Any help would be SUPER appreciated.