Forum Discussion

Dfuhrman8's avatar
Dfuhrman8
Copper Contributor
May 02, 2022

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

  • Dfuhrman8 

    Perhaps change

     

    ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet3!S:AC, Sheet4!CR)>0"

     

    to

     

    ActiveCell.Formula = "=COUNTIF(Sheet3!S:AC,CR1)=COUNTIF(CR:CR,CR1)"

    • Dfuhrman8's avatar
      Dfuhrman8
      Copper Contributor
      That did not work. It only worked for some of the cells, but did not count on all of them.
      • HansVogelaar's avatar
        HansVogelaar
        MVP

        Dfuhrman8 

        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.