Forum Discussion
Excel - VBA Help - Two Sheets matching amounts
I have two sheets that I need to compare from each other. On sheet 1, in column CR, I have a column of amounts, that I need to verify if all are listed, compare to Sheet 2, columns, S-AC. there are duplicate amounts listed, so I need to make sure if for example, $15.00 is listed 4 times on sheet 1, then on sheet 2 $15.00 is listed four times. I am just wanting a true or false response.
I was using the following VBA Script, but what I found is that it is not counting how many are listed. So in column CR of sheet 1 I have $15.00 listed 4 times, however in sheet 2, $15.00 is only showing listed 2 times in columns S-AC.
VBA Script using:
Sub ERROR_REVIEW()
ERROR_REVIEW Macro
ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet3!S:AC, Sheet4!CR)>0"
Range("CS1"). Select
Selection.AutoFill Destination: =Range("CS1:CS100")
Range ("CS1:CS100").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula:="FALSE"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End with
Selection.FormatConditions(1).StopIfTrue = False
Range ("CT1").Select
End Sub
13 Replies
Perhaps change
ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet3!S:AC, Sheet4!CR)>0"
to
ActiveCell.Formula = "=COUNTIF(Sheet3!S:AC,CR1)=COUNTIF(CR:CR,CR1)"
- Dfuhrman8Copper ContributorThat did not work. It only worked for some of the cells, but did not count on all of them.
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.