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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,316
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
46,316
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,994
Messages
5,526,125
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top