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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,658
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,658
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,827
Messages
5,855,878
Members
431,771
Latest member
CoryMelth

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
Top