Incorrect For Next Structure

[keith0528]

Greetings VBA'ers,

I have some really cool code that compares 2 columns and if they don't match it colors them red. There is even a nifty column number to letter conversion Function. But there is a problem with the For Next set up.
I need to be comparing row A2 with row B2, row A3 with row B3 etc. The first pass is right but then the 2nd pass onward its comparing row A2 with B3, then A2 with B4 etc. The 1st For Next doesn't run again. Can someone please show me how to correct this and help yourself to the code if you like.

Sub CompareColumnValues()
Dim ColumnNumber As Integer
Dim LstRw As Integer
Dim NumtoCol As String
Dim Found1 As Range
Dim CompareString As String
Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
LstRw = ActiveSheet.Range("A65536").End(xlUp).Row
'MyString = Left(Range("B2"), InStr(Range("B2"), "_") - 1)
'MyString = Split(Range("B3"), "_")(0)
Set Found1 = Cells.Find(What:="Ticket_Carrier", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
       False, SearchFormat:=False)
       
       Found1.Select
       ColumnNumber = Selection.Column
       
       
    'call to function, convert column number to letter
    NumtoCol = ConvertToLetter(ColumnNumber)
    
   
    If Found1 Is Nothing Then
        MsgBox "Ticket_Carrier column not found"
    End If
    
    For i = 2 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        Set rng1 = Sheets("Sheet1").Range("A" & i)
        
        For j = 2 To Sheets("Sheet1").Range(NumtoCol & Rows.Count).End(xlUp).Row
            Set rng2 = Sheets("Sheet1").Range(NumtoCol & j)
            
            
            CompareString = Split(Range(NumtoCol & j), "_")(0)
            'rng2 = CompareString
            
            If CompareString = rng1 Then
                'nothing
            Else
                rng1.Interior.Color = RGB(255, 0, 0)
            End If
                     
            'Set rng2 = Nothing
            'Set rng1 = Nothing
        Next j
        
    Next i
End Sub
Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

thanks

 

[shg]

Maybe (completely untested) ...

Sub CompareColumnValues()
    Dim rFind       As Range
    Dim iCol        As Long
    Dim iRow        As Long

    Set rFind = Cells.Find(What:="Ticket_Carrier", _
                           LookIn:=xlValues, _
                           LookAt:=xlWhole, _
                           MatchCase:=False)

    If rFind Is Nothing Then
        MsgBox "Ticket_Carrier column not found"
        Exit Sub
    End If

    iCol = rFind.Column

    For iRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        With Rows(iRow)
            If .Cells(1).Value <> Split(.Cells(iCol).Value)(0) Then
                .Cells(1).Interior.Color = vbRed
            End If
        End With
    Next iRow
End Sub

 

[shg]

Oops:

           If .Cells(1).Value <> Split(.Cells(iCol).Value, [COLOR="#FF0000"]"_"[/COLOR])(0) Then

 

[keith0528]

Oops:

           If .Cells(1).Value <> Split(.Cells(iCol).Value, [COLOR=#FF0000]"_"[/COLOR])(0) Then

Hi Shg,

Thank you for response. Your code is close however, it is seeing the values as different and coloring every cell in col A red. If u have any suggestions I'm all ears. Meanwhile, i'll keep playing with it.

thank you,
Keith

 

[keith0528]

Hi Shg,

Thank you for response. Your code is close however, it is seeing the values as different and coloring every cell in col A red. If u have any suggestions I'm all ears. Meanwhile, i'll keep playing with it.

thank you,
Keith

Hi Shg - can u tell me what ".Cells(1).Value" is doing?

 

[keith0528]

Hi Shg - can u tell me what ".Cells(1).Value" is doing?

Hi Shg - just for grins, I made the 2nd column exactly the same as column A just so it wouldn't have to split out anything and the code still executes the color red function. I'm unable to see what values are getting picked up in the .cells(1).value so I can't tell if it's actually grabbing the cell value or not.

regards,
Keith

 

[shg]

I'm flying blind -- I have no idea what the data looks like. Put a simple example on box.net and post a link.

 

[keith0528]

I'm flying blind -- I have no idea what the data looks like. Put a simple example on box.net and post a link.

Hi Shg,

No problem. Here is the link https://app.box.com/s/g6efq8raxjjfcew6q58i

The example spreadsheet has my macro included. I can see where you were going with your code and you are on the right track.
My code correctly ignores everything to the right of and including the underscore to make an apples to apples comparison but my nested For Next loops are screwed up. I can't figure out how to merge the comparison logic of 2 columns into one loop stmt.

thanks for working with me. I really appreciate it.

 

[shg]

In my code, change

            If .Cells(1).[COLOR="#FF0000"]Value[/COLOR] <> Split(.Cells(iCol).Value, "_")(0) Then

to

            If .Cells(1).[COLOR="#FF0000"]Text [/COLOR]<> Split(.Cells(iCol).Value, "_")(0) Then

 

[keith0528]

Shg - Apologies for just now getting back. That code works. Well done!

thanks,
Keith