loop through riow & columns & match each cell in row

cds

Board Regular
Joined
Mar 25, 2012
Messages
84
I have data wtih 21 rows & 6 columns. Wherein I need to compare a1 with a2 to f2 similarly ,b1,c1,d1,e1 & f1 with a2 to f2 and then increment row by 1 and continue the same process till end. If the result matches then highlight the previous row cell . if the result is already highlighted because of previous match then make it bold and then on next match underline it . My data is something like this

a b c d e f
1 2 5 7 4 3 9
2 5 4 5 5 7 4
3 2 3 7 8 9 12
4 1 8 9 7 5 8


I have the following vba which matches with a1=a2,b1=b2, c1=c2 and ignore b2=a1,c2=b1 and so on. Kindly guide me to correct the vba to make it bold & underline it with success matches while comparing previous row wtih nextrow

Sub colorCellMacro2()
Dim i As Long


Dim firstRow As Integer
Dim secondRow As Integer




firstRow = 1
secondRow = 2


Do While Cells(firstRow, 1) <> "" And Cells(secondRow, 1) <> ""


For col = 1 To 6
For row = 1 To 21

colval1 = Cells(firstRow, col).Value
colval2 = Cells(secondRow, col).Value

If colval1 = colval2 Then

Cells(firstRow, col).Select

End With
Next row

Next col
firstRow = firstRow + 1
secondRow = secondRow + 1
Loop
MsgBox "complete"

End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
How about
Code:
Sub colorCellMacro2()
   Dim r As Long, c As Long, cc As Long
   For r = 1 To 21
      For c = 1 To 6
         For cc = 1 To 6
            If Cells(r, c) = Cells(r + 1, cc) Then
               If Cells(r, c).Interior.ColorIndex <> 37 Then
                  Cells(r, c).Interior.ColorIndex = 37
               ElseIf Cells(r, c).Font.Bold = False Then
                  Cells(r, c).Font.Bold = True
               Else
                  Cells(r, c).Font.Underline = True
               End If
            End If
         Next cc
      Next c
   Next r
End Sub
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,217,435
Messages
6,136,611
Members
450,021
Latest member
Jlopez0320

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top