Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight
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. "Msgbox "Data not found"" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight
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 -- Regards Roger Govier Msgbox Data not found wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight
Thanks Mike!! It worked!!! "Mike H" wrote: 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. "Msgbox "Data not found"" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to highlight more related cells if cell highlight | Excel Discussion (Misc queries) | |||
Highlight if ... | Excel Worksheet Functions | |||
Highlight active cell and de-highlight previous cell | Excel Programming | |||
Highlight | Excel Discussion (Misc queries) | |||
Highlight cells with ctrl-click but only un-highlight one cell | Excel Discussion (Misc queries) |