J
Jeff
see anything wrong with this, it cause excel 2002 to "not respond" when the
button is clicked
Private Sub Button2_Click()
Dim IIndex As Integer
Dim JIndex As Integer
Dim Tests As Integer
Dim Fill As Integer
Dim Ctr As Integer
Dim IIxx As Integer
Dim JIxx As Integer
Dim CbeRange As Range
Dim Myrow As Range
Dim Mycol As Range
Dim MC As Integer
Dim MR As Integer
Dim CB As Integer
Ctr = 0
For IIndex = 7 To 15
For JIndex = 2 To 10
If Ctr <> 1 And IsNull(Cells(IIndex, JIndex)) Then
If 2 <= JIndex And JIndex <= 4 And 7 <= IIndex And IIndex <= 9 Then
CbeRange = Range("B79")
If 5 <= JIndex And JIndex <= 7 And 7 <= IIndex And IIndex <= 9 Then
CbeRange = Range("E7:G9")
If 8 <= JIndex And JIndex <= 10 And 7 <= IIndex And IIndex <= 9 Then
CbeRange = Range("H7:J9")
If 2 <= JIndex And JIndex <= 4 And 10 <= IIndex And IIndex <= 12 Then
CbeRange = Range("B1012")
If 5 <= JIndex And JIndex <= 7 And 10 <= IIndex And IIndex <= 12 Then
CbeRange = Range("E10:G12")
If 8 <= JIndex And JIndex <= 10 And 10 <= IIndex And IIndex <= 12 Then
CbeRange = Range("H10:J12")
If 2 <= JIndex And JIndex <= 4 And 13 <= IIndex And IIndex <= 15 Then
CbeRange = Range("B1315")
If 5 <= JIndex And JIndex <= 7 And 13 <= IIndex And IIndex <= 15 Then
CbeRange = Range("E13:G15")
If 8 <= JIndex And JIndex <= 10 And 13 <= IIndex And IIndex <= 15 Then
CbeRange = Range("H13:J15")
If 7 = IIndex Then Myrow = Range("B7:J7")
If 8 = IIndex Then Myrow = Range("B8:J8")
If 9 = IIndex Then Myrow = Range("B9:J9")
If 10 = IIndex Then Myrow = Range("B10:J10")
If 11 = IIndex Then Myrow = Range("B11:J11")
If 12 = IIndex Then Myrow = Range("B12:J12")
If 13 = IIndex Then Myrow = Range("B13:J13")
If 14 = IIndex Then Myrow = Range("B14:J14")
If 15 = IIndex Then Myrow = Range("B15:J15")
If 2 = JIndex Then Mycol = Range("B7:B15")
If 3 = JIndex Then Mycol = Range("C7:C15")
If 4 = JIndex Then Mycol = Range("D715")
If 5 = JIndex Then Mycol = Range("E7:E15")
If 6 = JIndex Then Mycol = Range("F7:F15")
If 7 = JIndex Then Mycol = Range("G7:G15")
If 8 = JIndex Then Mycol = Range("H7:H15")
If 9 = JIndex Then Mycol = Range("I7:I15")
If 10 = JIndex Then Mycol = Range("J7:J15")
Ctr = 0
For Tests = 1 To 9
MC = 0
If Range(Mycol).Find(Tests, LookIn:=xlValues) Then MC = 1
MR = 0
If Range(Myrow).Find(Tests, LookIn:=xlValues) Then MR = 1
CB = 0
If Range(CbeRange).Find(Tests, LookIn:=xlValues) Then CB = 1
If MC = 0 And MR = 0 And CB = 0 Then
Ctr = Ctr + 1
Fill = Tests
JIxx = JIndex
IIxx = IIndex
End If
Next Tests
If Ctr = 1 Then
Cells(IIxx, JIxx) = Fill
Cells(IIxx, JIxx).Font.Bold = True
Cells(IIxx, JIxx).Font.Color = 5
End If
End If
Next JIndex
Next IIndex
Loop
End Sub
button is clicked
Private Sub Button2_Click()
Dim IIndex As Integer
Dim JIndex As Integer
Dim Tests As Integer
Dim Fill As Integer
Dim Ctr As Integer
Dim IIxx As Integer
Dim JIxx As Integer
Dim CbeRange As Range
Dim Myrow As Range
Dim Mycol As Range
Dim MC As Integer
Dim MR As Integer
Dim CB As Integer
Ctr = 0
For IIndex = 7 To 15
For JIndex = 2 To 10
If Ctr <> 1 And IsNull(Cells(IIndex, JIndex)) Then
If 2 <= JIndex And JIndex <= 4 And 7 <= IIndex And IIndex <= 9 Then
CbeRange = Range("B79")
If 5 <= JIndex And JIndex <= 7 And 7 <= IIndex And IIndex <= 9 Then
CbeRange = Range("E7:G9")
If 8 <= JIndex And JIndex <= 10 And 7 <= IIndex And IIndex <= 9 Then
CbeRange = Range("H7:J9")
If 2 <= JIndex And JIndex <= 4 And 10 <= IIndex And IIndex <= 12 Then
CbeRange = Range("B1012")
If 5 <= JIndex And JIndex <= 7 And 10 <= IIndex And IIndex <= 12 Then
CbeRange = Range("E10:G12")
If 8 <= JIndex And JIndex <= 10 And 10 <= IIndex And IIndex <= 12 Then
CbeRange = Range("H10:J12")
If 2 <= JIndex And JIndex <= 4 And 13 <= IIndex And IIndex <= 15 Then
CbeRange = Range("B1315")
If 5 <= JIndex And JIndex <= 7 And 13 <= IIndex And IIndex <= 15 Then
CbeRange = Range("E13:G15")
If 8 <= JIndex And JIndex <= 10 And 13 <= IIndex And IIndex <= 15 Then
CbeRange = Range("H13:J15")
If 7 = IIndex Then Myrow = Range("B7:J7")
If 8 = IIndex Then Myrow = Range("B8:J8")
If 9 = IIndex Then Myrow = Range("B9:J9")
If 10 = IIndex Then Myrow = Range("B10:J10")
If 11 = IIndex Then Myrow = Range("B11:J11")
If 12 = IIndex Then Myrow = Range("B12:J12")
If 13 = IIndex Then Myrow = Range("B13:J13")
If 14 = IIndex Then Myrow = Range("B14:J14")
If 15 = IIndex Then Myrow = Range("B15:J15")
If 2 = JIndex Then Mycol = Range("B7:B15")
If 3 = JIndex Then Mycol = Range("C7:C15")
If 4 = JIndex Then Mycol = Range("D715")
If 5 = JIndex Then Mycol = Range("E7:E15")
If 6 = JIndex Then Mycol = Range("F7:F15")
If 7 = JIndex Then Mycol = Range("G7:G15")
If 8 = JIndex Then Mycol = Range("H7:H15")
If 9 = JIndex Then Mycol = Range("I7:I15")
If 10 = JIndex Then Mycol = Range("J7:J15")
Ctr = 0
For Tests = 1 To 9
MC = 0
If Range(Mycol).Find(Tests, LookIn:=xlValues) Then MC = 1
MR = 0
If Range(Myrow).Find(Tests, LookIn:=xlValues) Then MR = 1
CB = 0
If Range(CbeRange).Find(Tests, LookIn:=xlValues) Then CB = 1
If MC = 0 And MR = 0 And CB = 0 Then
Ctr = Ctr + 1
Fill = Tests
JIxx = JIndex
IIxx = IIndex
End If
Next Tests
If Ctr = 1 Then
Cells(IIxx, JIxx) = Fill
Cells(IIxx, JIxx).Font.Bold = True
Cells(IIxx, JIxx).Font.Color = 5
End If
End If
Next JIndex
Next IIndex
Loop
End Sub