Excel - VBA Help - Two Sheets matching amounts

[Dfuhrman8]

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

[#2]

@Dfuhrman8 

Perhaps change

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

to

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

[#3]

That did not work. It only worked for some of the cells, but did not count on all of them.

[#4]

@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.

[#5]

@Hans VogelaarI have attached a sample workbook. I need to verify that all the amounts from sheet 2 are on sheet 1.

[#6]

@Dfuhrman8 

Your request in the first post was "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."

That suggests creating a rule for column A on Sheet1.

Now you write "I need to verify that all the amounts from sheet 2 are on sheet 1."

That suggests creating a rule for Sheet2.

Which is it?

[#7]

I need to verify that all amounts from sheet 2 or listed on sheet 1. There are going to be times that the amounts will not be there, and that is okay. I had it as a TRUE or FALSE output, so the FALSE once I will review.

[#8]

@Dfuhrman8 

I can't say that's entirely clear to me, but if I understand you correctly, it's the opposite of what you originally asked.

I created a conditional formatting rule for Sheet2. See the attached version.

[#9]

I need a formula that will take a list of values from one sheet, and compare them to a different sheet. Sheet 1 has column A with amounts and then column B with type of charge. Sheet 2 has the type of charges going across in a row - travel, training, meals, etc, and then amounts going down in each column. I need a formula to verify that sheet 1 has all of the information from sheet 2.

[#10]

@Dfuhrman8 

Please attach a sample workbook.

[#11]

@Hans Vogelaar 

Attached

[#12]

@Dfuhrman8 

See the attached version. You'll see that the entire column B is highlighted. This is because the values in column B are text instead of numbers.

And most of column M is highlighted. This is because the category Ttl_Fee does not occur on Sheet1.

[#13]

I understand on the Ttl_Fee. My question is how did you figure it? Formula?VBA? Conditional Formating? I have to do this every month.

[#14]

@Dfuhrman8 

It's conditional formatting of type 'Use a formula to determine which cells to format'.

Select A2 on the second sheet, then click Conditional Formatting > Manage Rules... > Edit Rule to see the formula that I used.