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
the webdesigner group security shielf
the webdesigner group logo

Close Button

Web Page Design by

The Web Designer Group