Highlight

  • Thread starter Msgbox Data not found
  • 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
 
M

Mike H

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.
 
R

Roger Govier

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
 
M

Msgbox Data not found

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...
 
Top