Validate an adjustment amount matches the right code based on another worksheet

[AndyTampa]

I'm trying to create a tool we can use that automatically validates the data entered on it. I'm having difficulty putting my formula into a VBA alternative.

On the main sheet are the two columns I'm concerned with at the moment. There is an Amount column and a Code column. I have a separate worksheet where a table contains the codes for credits and charges. As my macro runs down the main sheet line-by-line, validating the data, I need it to check these two cells on each line and perform actions based on the tests. Here is the formula from which I'm trying to build code.

=IF(NOT($D17=""),IF(OR(AND(COUNTIF(AdjCodes!$A:$A,$D17)>0,$C17>0),AND(COUNTIF(AdjCodes!$B:$B,$D17)>0,$C17<0)),"VALID","INVALID"),"INVALID")

The formula works as a test, but instead of putting Invalid or Valid in a cell, I want to highlight cells that are invalid and increase counters I have set up. As important, if not more so, is the order the tests are done. I'm confusing myself with all the If/Thens running through my head. The monetary tests are moot if the codes don't exist on the other sheet.

Should I be testing for correctness first in an IF statement of some kind so the tests only get done in the ELSE statement? For example: IF (the code is a charge AND the amount is over 0) OR (the code is a credit AND the amount is under 0), don't test anything else. ELSE run through the tests. Those are the only two ways this data is valid. How would I write that?

The tests I need to do if the pair are invalid are:
1) If the Amount cell is blank or 0.00, increase the invalid data count by one and highlight the amount cell.
2) If the Code cell is blank or doesn't exist in the Codes table on the AdjCodes sheet, increase the invalid data count by one and highlight the Code cell.

If those conditions exist, there is no point in checking for the "polarity" of the adjustment. Testing the Amount cells should be skipped to avoid marking one bad cell in two different counters (I think ).

3) If the code is in the Charge column of the Codes table, but the amount is less than 0.00, increase the Adjustment Mismatch count by one and highlight both cells
4) If the code is in the Credit column of the Codes table, but the amount is more than 0.00, increase the Adjustment Mismatch count by one and highlight both cells

I'm missing something. There appear to be overlaps in my tests.

 

[AndyTampa]

As I continue to try to figure this out, I think VBA may simply allow me to see if the code simply exists in a range instead of using Countif. Thoughts?

 

[AndyTampa]

I've come up with this so far:

    If WorksheetFunction.CountIf(Sheets("AdjCodes").Range("A:A"), Range("D" & RNum)) > 0 And Range("C" & RNum).Value > 0 And Not Range("D" & RNum) = "" Then GoTo CodeVal
    If WorksheetFunction.CountIf(Sheets("AdjCodes").Range("B:B"), Range("D" & RNum)) > 0 And Range("C" & RNum).Value < 0 And Not Range("D" & RNum) = "" Then GoTo CodeVal

    If Cells(RNum, 3).Value = "" Or Cells(RNum, 3) = 0 Then
        Cells(RNum, 3).Interior.ColorIndex = 22
        InvEnt = InvEnt + 1
        Range("J4").Value = InvEnt & " Invalid Data"
    End If
    If Cells(RNum, 4).Value = "" Or WorksheetFunction.CountIf(Sheets("AdjCodes").Range("A:B"), Cells(RNum, 4)) < 1 Then
        Cells(RNum, 4).Interior.ColorIndex = 22
        InvEnt = InvEnt + 1
        Range("J4").Value = InvEnt & " Invalid Data"
    End If
    If WorksheetFunction.CountIf(Sheets("AdjCodes").Range("A:A"), Range("D" & RNum)) > 0 And Range("C" & RNum).Value < 0 And Not Range("D" & RNum) = "" Then
        Range("C" & RNum & ":D" & RNum).Interior.ColorIndex = 44
        ADJErr = ADJErr + 1
        Range("J5").Value = ADJErr & " Adjustment Mismatch Errors"
    End If
    If WorksheetFunction.CountIf(Sheets("AdjCodes").Range("B:B"), Range("D" & RNum)) > 0 And Range("C" & RNum).Value > 0 And Not Range("D" & RNum) = "" Then
        Range("C" & RNum & ":D" & RNum).Interior.ColorIndex = 44
        ADJErr = ADJErr + 1
        Range("J5").Value = ADJErr & " Adjustment Mismatch Errors"
    End If

CodeVal:

But there are duplicate tests in there and I don't know how to condense them. I have two separate complex IF statements to test for correct code/amount pairs and two of almost the same test to check for the reverse, then perform the same task.

How would I write:
IF (#1, #2, #3 are all true) OR (#4, #5, #6 are all true) in a single If statement?

I'm open to suggestions of a better way of doing this.

 

[AndyTampa]

I'm still looking for help on this part of my project. I've also run into another snag. It seems that my tool doesn't know that the amount cells don't contain numbers.

I've tried the following:

    If Cells(RNum, 3).Value = "" Or Cells(RNum, 3) = 0 Or IsNumeric(Cells(RNum, 3).Value) = False Then
        Cells(RNum, 3).Interior.ColorIndex = 22
        InvEnt = InvEnt + 1
        Range("J4").Value = InvEnt & " Invalid Data"

While the added part of the If statement works by itself, I can't get it to work in this If statement. What is wrong with this?