Count if number is on any other sheet


With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

OTP = 2

For n = 1 To lastrow

srce = ActiveSheet.Range("A" & n)

For x = 1 To 25

If srce = Worksheets("Control").Range("A" & x) Then

nme1 = ActiveSheet.Cells(n, Worksheets("Control").Range("B" & x))
nme2 = ActiveSheet.Cells(n, Worksheets("Control").Range("C" & x))
add1 = ActiveSheet.Cells(n, Worksheets("Control").Range("D" & x))
add2 = ActiveSheet.Cells(n, Worksheets("Control").Range("E" & x))
add3 = ActiveSheet.Cells(n, Worksheets("Control").Range("F" & x))
add4 = ActiveSheet.Cells(n, Worksheets("Control").Range("G" & x))
pcode = ActiveSheet.Cells(n, Worksheets("Control").Range("H" & x))
tel1 = ActiveSheet.Cells(n, Worksheets("Control").Range("I" & x))
tel2 = ActiveSheet.Cells(n, Worksheets("Control").Range("J" & x))
tel3 = ActiveSheet.Cells(n, Worksheets("Control").Range("K" & x))

Worksheets("Output").Range("A" & OTP) = srce
Worksheets("Output").Range("B" & OTP) = nme1
Worksheets("Output").Range("C" & OTP) = nme2
Worksheets("Output").Range("D" & OTP) = add1
Worksheets("Output").Range("E" & OTP) = add2
Worksheets("Output").Range("F" & OTP) = add3
Worksheets("Output").Range("G" & OTP) = add4
Worksheets("Output").Range("H" & OTP) = pcode
Worksheets("Output").Range("I" & OTP) = tel1
Worksheets("Output").Range("J" & OTP) = tel2
Worksheets("Output").Range("K" & OTP) = tel3

OTP = OTP + 1

End If

Next x

Next n

'Check TPDATA
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For Z = 2 To lastrow

cnt = 0
cnt1 = 0
cnt2 = 0
cnt3 = 0
cnt4 = 0
cnt5 = 0
cnt6 = 0
cnt7 = 0
cnt8 = 0
cnt9 = 0
cnt10 = 0
cnt11 = 0
cnt12 = 0

cnt1 = WorksheetFunction.CountIf(Worksheets("1").Range("A:A"), ActiveSheet.Range("I" & Z))
cnt2 = WorksheetFunction.CountIf(Worksheets("2").Range("A:A"), ActiveSheet.Range("I" & Z))
cnt3 = WorksheetFunction.CountIf(Worksheets("3").Range("A:A"), ActiveSheet.Range("I" & Z))
cnt4 = WorksheetFunction.CountIf(Worksheets("4").Range("A:A"), ActiveSheet.Range("I" & Z))
cnt5 = WorksheetFunction.CountIf(Worksheets("5").Range("A:A"), ActiveSheet.Range("I" & Z))
cnt6 = WorksheetFunction.CountIf(Worksheets("6").Range("A:A"), ActiveSheet.Range("I" & Z))
cnt7 = WorksheetFunction.CountIf(Worksheets("7").Range("A:A"), ActiveSheet.Range("I" & Z))
cnt8 = WorksheetFunction.CountIf(Worksheets("8").Range("A:A"), ActiveSheet.Range("I" & Z))
cnt9 = WorksheetFunction.CountIf(Worksheets("9").Range("A:A"), ActiveSheet.Range("I" & Z))
cnt10 = WorksheetFunction.CountIf(Worksheets("10").Range("A:A"), ActiveSheet.Range("I" & Z))
cnt11 = WorksheetFunction.CountIf(Worksheets("11").Range("A:A"), ActiveSheet.Range("I" & Z))
cnt12 = WorksheetFunction.CountIf(Worksheets("12").Range("A:A"), ActiveSheet.Range("I" & Z))

cnt = Val(cnt1) + Val(cnt2) + Val(cnt3) + Val(cnt4) + Val(cnt5) + Val(cnt6) + Val(cnt7) + Val(cnt8) + Val(cnt9) + Val(cnt10) + Val(cnt11) + Val(cnt12)

If cnt <> 0 Then ActiveSheet.Range("I" & Z).Interior.Color = 255

cnt = 0
cnt1 = 0
cnt2 = 0
cnt3 = 0
cnt4 = 0
cnt5 = 0
cnt6 = 0
cnt7 = 0
cnt8 = 0
cnt9 = 0
cnt10 = 0
cnt11 = 0
cnt12 = 0

cnt1 = WorksheetFunction.CountIf(Worksheets("1").Range("A:A"), ActiveSheet.Range("J" & Z))
cnt2 = WorksheetFunction.CountIf(Worksheets("2").Range("A:A"), ActiveSheet.Range("J" & Z))
cnt3 = WorksheetFunction.CountIf(Worksheets("3").Range("A:A"), ActiveSheet.Range("J" & Z))
cnt4 = WorksheetFunction.CountIf(Worksheets("4").Range("A:A"), ActiveSheet.Range("J" & Z))
cnt5 = WorksheetFunction.CountIf(Worksheets("5").Range("A:A"), ActiveSheet.Range("J" & Z))
cnt6 = WorksheetFunction.CountIf(Worksheets("6").Range("A:A"), ActiveSheet.Range("J" & Z))
cnt7 = WorksheetFunction.CountIf(Worksheets("7").Range("A:A"), ActiveSheet.Range("J" & Z))
cnt8 = WorksheetFunction.CountIf(Worksheets("8").Range("A:A"), ActiveSheet.Range("J" & Z))
cnt9 = WorksheetFunction.CountIf(Worksheets("9").Range("A:A"), ActiveSheet.Range("J" & Z))
cnt10 = WorksheetFunction.CountIf(Worksheets("10").Range("A:A"), ActiveSheet.Range("J" & Z))
cnt11 = WorksheetFunction.CountIf(Worksheets("11").Range("A:A"), ActiveSheet.Range("J" & Z))
cnt12 = WorksheetFunction.CountIf(Worksheets("12").Range("A:A"), ActiveSheet.Range("J" & Z))

cnt = Val(cnt1) + Val(cnt2) + Val(cnt3) + Val(cnt4) + Val(cnt5) + Val(cnt6) + Val(cnt7) + Val(cnt8) + Val(cnt9) + Val(cnt10) + Val(cnt11) + Val(cnt12)

If cnt <> 0 Then ActiveSheet.Range("J" & Z).Interior.Color = 255

cnt = 0
cnt1 = 0
cnt2 = 0
cnt3 = 0
cnt4 = 0
cnt5 = 0
cnt6 = 0
cnt7 = 0
cnt8 = 0
cnt9 = 0
cnt10 = 0
cnt11 = 0
cnt12 = 0

cnt1 = WorksheetFunction.CountIf(Worksheets("1").Range("A:A"), ActiveSheet.Range("K" & Z))
cnt2 = WorksheetFunction.CountIf(Worksheets("2").Range("A:A"), ActiveSheet.Range("K" & Z))
cnt3 = WorksheetFunction.CountIf(Worksheets("3").Range("A:A"), ActiveSheet.Range("K" & Z))
cnt4 = WorksheetFunction.CountIf(Worksheets("4").Range("A:A"), ActiveSheet.Range("K" & Z))
cnt5 = WorksheetFunction.CountIf(Worksheets("5").Range("A:A"), ActiveSheet.Range("K" & Z))
cnt6 = WorksheetFunction.CountIf(Worksheets("6").Range("A:A"), ActiveSheet.Range("K" & Z))
cnt7 = WorksheetFunction.CountIf(Worksheets("7").Range("A:A"), ActiveSheet.Range("K" & Z))
cnt8 = WorksheetFunction.CountIf(Worksheets("8").Range("A:A"), ActiveSheet.Range("K" & Z))
cnt9 = WorksheetFunction.CountIf(Worksheets("9").Range("A:A"), ActiveSheet.Range("K" & Z))
cnt10 = WorksheetFunction.CountIf(Worksheets("10").Range("A:A"), ActiveSheet.Range("K" & Z))
cnt11 = WorksheetFunction.CountIf(Worksheets("11").Range("A:A"), ActiveSheet.Range("K" & Z))
cnt12 = WorksheetFunction.CountIf(Worksheets("12").Range("A:A"), ActiveSheet.Range("K" & Z))

cnt = Val(cnt1) + Val(cnt2) + Val(cnt3) + Val(cnt4) + Val(cnt5) + Val(cnt6) + Val(cnt7) + Val(cnt8) + Val(cnt9) + Val(cnt10) + Val(cnt11) + Val(cnt12)

If cnt <> 0 Then ActiveSheet.Range("K" & Z).Interior.Color = 255

End If

Next Z






Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology