Compare cells vba

Hello,
Can you  please help,
Is there a macro that I can run to compare 2 Columns then highlight the lower value between cells.

Example,
Column C AND Column T
Compare C2 with T2 Highlight lower value
Compare C3 with T3 Highlight lower value
Compare C4 with T4 Highlight lower value
AND So on.

Column D AND Column U
Compare D2 with U2 Highlight lower value
Compare D3 with U3 Highlight lower value
Compare D4 with U4 Highlight lower value
AND So on.

Column E AND Column V
AND So on.

Number of Rows differ
Thank you,
W.E.BAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Any particular reason you want to do this by macro as you can easily do this with conditional formatting...and which will be as effective as macro..

Saurabh...
0
W.E.BAuthor Commented:
This will be part of another macro that I'm running on my sheets.
0
Martin LissOlder than dirtCommented:
What if the values are the same?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

W.E.BAuthor Commented:
Good question,
Highlight both with some color..
0
Martin LissOlder than dirtCommented:
The type of highlighting can be changed.

Sub Compare()
Dim lngLastRow As Long
Dim lngRow As Long

lngLastRow = Range("C1048576").End(xlUp).Row

For lngRow = 1 To lngLastRow
    If Cells(lngRow, "C") <> "" Then
        If Cells(lngRow, "C") > Cells(lngRow, "T") Then
            Cells(lngRow, "T").Font.Color = vbRed
        ElseIf Cells(lngRow, "C") < Cells(lngRow, "T") Then
            Cells(lngRow, "C").Font.Color = vbRed
        Else
            Cells(lngRow, "C").Font.Color = vbRed
            Cells(lngRow, "T").Font.Color = vbRed
        End If
    End If
Next
    
lngLastRow = Range("D1048576").End(xlUp).Row

For lngRow = 1 To lngLastRow
    If Cells(lngRow, "D") <> "" Then
        If Cells(lngRow, "D") > Cells(lngRow, "U") Then
            Cells(lngRow, "U").Font.Color = vbRed
        ElseIf Cells(lngRow, "D") < Cells(lngRow, "U") Then
            Cells(lngRow, "D").Font.Color = vbRed
        Else
            Cells(lngRow, "D").Font.Color = vbRed
            Cells(lngRow, "U").Font.Color = vbRed
        End If
    End If
Next

lngLastRow = Range("E1048576").End(xlUp).Row

For lngRow = 1 To lngLastRow
    If Cells(lngRow, "E") <> "" Then
        If Cells(lngRow, "E") > Cells(lngRow, "V") Then
            Cells(lngRow, "V").Font.Color = vbRed
        ElseIf Cells(lngRow, "E") < Cells(lngRow, "V") Then
            Cells(lngRow, "E").Font.Color = vbRed
        Else
            Cells(lngRow, "E").Font.Color = vbRed
            Cells(lngRow, "v").Font.Color = vbRed
        End If
    End If
Next
    
End Sub

Open in new window

0
Saurabh Singh TeotiaCommented:
Martin,

Nice Solution, But not sure why you have 3 loops in their as i understand you want to check for last row for each column and then run loop but lets say if we find generic last row and run one loop basis of it that will be still faster...

What i mean something like this..

Option Explicit

Sub comparecolumns()

    Dim lrow As Long, k As Long

    lrow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

    For k = 2 To lrow

        If Cells(k, "c").Value <> "" And Cells(k, "t").Value <> "" Then

            If Cells(k, "c").Value > Cells(k, "t").Value Then
                Cells(k, "t").Interior.ColorIndex = 3
            ElseIf Cells(k, "c").Value < Cells(k, "t").Value Then
                Cells(k, "c").Interior.ColorIndex = 3
            Else
                Cells(k, "t").Interior.ColorIndex = 3
                Cells(k, "c").Interior.ColorIndex = 3
            End If

        End If


     If Cells(k, "d").Value <> "" And Cells(k, "u").Value <> "" Then

            If Cells(k, "d").Value > Cells(k, "u").Value Then
                Cells(k, "u").Interior.ColorIndex = 3
            ElseIf Cells(k, "d").Value < Cells(k, "u").Value Then
                Cells(k, "d").Interior.ColorIndex = 3
            Else
                Cells(k, "d").Interior.ColorIndex = 3
                Cells(k, "d").Interior.ColorIndex = 3
            End If

        End If
        
        
    
     If Cells(k, "e").Value <> "" And Cells(k, "v").Value <> "" Then

            If Cells(k, "e").Value > Cells(k, "v").Value Then
                Cells(k, "v").Interior.ColorIndex = 3
            ElseIf Cells(k, "e").Value < Cells(k, "v").Value Then
                Cells(k, "e").Interior.ColorIndex = 3
            Else
                Cells(k, "e").Interior.ColorIndex = 3
                Cells(k, "v").Interior.ColorIndex = 3
            End If

        End If



    Next k

End Sub

Open in new window


Saurabh...
0
W.E.BAuthor Commented:
Thank you guys,
I guess I will have to copy this for each Column, (I have between 12 and 26 columns)
is there a way to just have the main and add one Column?
IE:
k As Long
J1 = 3 To LastCol  

Thanks,
0
Saurabh Singh TeotiaCommented:
I'm assuming you are looking for this...

This will do the same thing compare column-c to column-t , column-d to column-u and column-e to column-v

Sub comparecolumns()

    Dim lrow As Long, k As Long, z As Long



    lrow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

    For k = 2 To lrow
        For z = 3 To 5
            If Cells(k, z).Value <> "" And Cells(k, z + 17).Value <> "" Then

                If Cells(k, z).Value > Cells(k, z + 17).Value Then
                    Cells(k, z + 17).Interior.ColorIndex = 3
                ElseIf Cells(k, z).Value < Cells(k, z + 17).Value Then
                    Cells(k, z).Interior.ColorIndex = 3
                Else
                    Cells(k, z + 17).Interior.ColorIndex = 3
                    Cells(k, z).Interior.ColorIndex = 3
                End If

            End If

        Next z
    Next k

End Sub

Open in new window


Saurabh...
0
Martin LissOlder than dirtCommented:
Here's a flexible version that doesn't care how many columns there are.

Sub Compare()
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngCol As Long
Dim lngLastColumn As Long

lngLastColumn = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
lngLastRow = ActiveSheet.UsedRange.Rows.Count

For lngCol = 1 To lngLastColumn
    For lngRow = 3 To lngLastRow
        If Cells(lngRow, lngCol) <> "" Then
            If Cells(lngRow, lngCol) > Cells(lngRow, lngCol).Offset(0, 17) Then
                Cells(lngRow, lngCol).Offset(0, 17).Font.Color = vbRed
            ElseIf Cells(lngRow, lngCol) < Cells(lngRow, lngCol).Offset(0, 17) Then
                Cells(lngRow, lngCol).Font.Color = vbRed
            Else
                Cells(lngRow, lngCol).Font.Color = vbRed
                Cells(lngRow, lngCol).Offset(0, 17).Font.Color = vbRed
            End If
        End If
    Next
Next

    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
W.E.BAuthor Commented:
Thank you very much.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.