sheet to sheet value comparison.

[jane23]

Hi,
I have code below to check if column F and D values are in other page, Column E,G,H should be in other page as well.
So I have code below. This code is not working correctly it ignores the comparison from other page.

Private Function rowLast()
    rowLast = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
End Function

Dim rngCell As Range
For Each rngCell In ActiveSheet.Range("F13:F" & rowLast)
  If Range("D" & rngCell.Row).Value = 0 And _        
Range("F" & rngCell.Row).Value = 0 Then

      ElseIf WorksheetFunction.CountIf(Sheets("CheckList").Range("B2:B" & ValidateL), rngCell) <> 0 And _
      WorksheetFunction.CountIf(Sheets("CheckList").Range("A2:A" & ValidateL), rngCell.Offset(, -2)) <> 0 And _
      WorksheetFunction.CountIf(Sheets("CheckList").Range("C2:C" & ValidateL), rngCell.Offset(, -1)) = 0 And _
      WorksheetFunction.CountIf(Sheets("CheckList").Range("D2:D" & ValidateL), rngCell.Offset(, 2)) = 0 And _
      WorksheetFunction.CountIf(Sheets("CheckList").Range("E2:E" & ValidateL), rngCell.Offset(, 1)) = 0 Then
       MsgBox "Please validate this combo " & rngCell & " Row Num: " & rngCell.Row
       End
    End If
    
Next
End Sub

First code does not work, but If I compare only IF column F and D values in other page and compare just E column, it works fine.

Dim rngCell As Range
For Each rngCell In ActiveSheet.Range("F13:F" & rowLast)
  If Range("D" & rngCell.Row).Value = 0 And _
        Range("F" & rngCell.Row).Value = 0 Then

      ElseIf WorksheetFunction.CountIf(Sheets("CheckList").Range("B2:B" & ValidateL), rngCell) <> 0 And _
      WorksheetFunction.CountIf(Sheets("CheckList").Range("A2:A" & ValidateL), rngCell.Offset(, -2)) <> 0 And _
      WorksheetFunction.CountIf(Sheets("CheckList").Range("E2:E" & ValidateL), rngCell.Offset(, 1)) = 0 Then
       MsgBox "Please validate this combo " & rngCell & " Row Num: " & rngCell.Row
       End
    End If
    
Next
End Sub

does anyone knows why first code is not working?

 

[PCL]

Can you detail your specifications:
It seems there is a test to do when column F and D value is 0
It seems taht the first row is 13
Let's restart from the beginning and explain what you want

 

[jane23]

Yes, If column F and D are 0 I would like to skip and validation is starting at F13. However if column F and D values are in column A(compares to D in main) and B(compares to F in main) in Checklist sheet, I would like to add validation that Column E,G,H from main sheet data must be from Checklist column C.D, and E.