Highlight

  • Thread starter Thread starter Msgbox Data not found
  • Start date Start date
M

Msgbox Data not found

Hi, everyone!!

I have a worksheet that has data in column A and the other in column B. Now
I want a VBA code that would higlight the data in column A if matches with
that in Column B. The code should search the whole A column for a data in B1
then start for B2.
I have a code mentioned below but it takes a long time but it works.
Any can help me for a quick code process?

Private Sub CommandButton1_Click()

Dim I As Integer

Worksheets("Sheet1").Activate
For I = 6 To 877
For J = 6 To 877
If Cells(I, 2).Value = Cells(J, 22).Value Then
Cells(I, 2).Interior.ColorIndex = 3
ElseIf Cells(I, 2).Value = "" Then
Cells(I, 2).Interior.ColorIndex = 4
End If
Next J
Next I
End Sub
 
Hi,

Try this

Private Sub CommandButton1_Click()
Dim LastRowA as long
Dim LastRowB as long
Dim MyRangeA as range, MyRangeB as range, c as range
Set sht = Sheets("Sheet1")
lastrowA = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
lastrowB = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRangeA = sht.Range("A6:A" & lastrowA)
Set MyRangeB = sht.Range("B6:B" & lastrowB)
For Each c In MyRangeA
If WorksheetFunction.CountIf(MyRangeB, c.Value) > 0 Then
c.Interior.ColorIndex = 3
Else
c.Interior.ColorIndex = 4
End If
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Hi

Use the Countif function to speed the process

Private Sub CommandButton1_Click()

Dim i As Integer

Worksheets("Sheet1").Activate
For i = 6 To 877

If WorksheetFunction.CountIf(Range("A6:A877") _
, Cells(i, 2).Value) > 0 Then
Cells(i, 2).Interior.ColorIndex = 3
ElseIf Cells(i, 2).Value = "" Then
Cells(i, 2).Interior.ColorIndex = 4
End If
Next i
End Sub
 
Thanks Mike!! It worked!!!
Mike H said:
Hi,

Try this

Private Sub CommandButton1_Click()
Dim LastRowA as long
Dim LastRowB as long
Dim MyRangeA as range, MyRangeB as range, c as range
Set sht = Sheets("Sheet1")
lastrowA = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
lastrowB = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRangeA = sht.Range("A6:A" & lastrowA)
Set MyRangeB = sht.Range("B6:B" & lastrowB)
For Each c In MyRangeA
If WorksheetFunction.CountIf(MyRangeB, c.Value) > 0 Then
c.Interior.ColorIndex = 3
Else
c.Interior.ColorIndex = 4
End If
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
Click to expand...
 
Back
Top