Writing a Comparison function for Excel Visual Basic

bill nguyen 5 Reputation points
2024-06-03T17:00:13.3633333+00:00

I'm new to Visual basic and I'm currently trying to write a simple piece of code to Compare value of the cell in 2 different columns. If the values are the same, then I want to fill in cell Green in a third column, used for the comparison result. If the values arent the same then the fill in color would be red. I tried using ChatGPT to help generate a basic code so that I can work with something and here is what it came up with.


Sub CompareColumns()

    Dim ws As Worksheet

    Dim col1 As Range, col2 As Range

    Dim cell1 As Range, cell2 As Range

    Dim lastRow As Long

    Dim i As Long

    

    ' Set your worksheet here

    Set ws = ThisWorkbook.Sheets("Sheet1")

    

    ' Set the columns you want to compare

    Set col1 = ws.Range("A:A")

    Set col2 = ws.Range("B:B")

    

    ' Find the last row in column 1

    lastRow = ws.Cells(ws.Rows.Count, col1.Column).End(xlUp).Row

    

    ' Loop through each cell in the columns

    For i = 1 To lastRow

        Set cell1 = col1.Cells(i, 1)

        Set cell2 = col2.Cells(i, 1)

        

        ' Compare the cells and color accordingly

        If cell1.Value = cell2.Value Then

            cell1.Interior.Color = RGB(0, 255, 0) ' Green

            cell2.Interior.Color = RGB(0, 255, 0) ' Green

        Else

            cell1.Interior.Color = RGB(255, 0, 0) ' Red

            cell2.Interior.Color = RGB(255, 0, 0) ' Red

        End If

    Next i

End Sub

I tried running the piece of code after I fixed the parameters to the columns that i intended to use the comparison on, but Excel gave me a 400 error code, and I looked up to see what that error code was, and apparently there are syntax errors in the code.

if anyone could please help me fix this code so that it can work and learn more, that would be great! thank you in advance.

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,653 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,684 questions
{count} vote

1 answer

Sort by: Most helpful
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more