Writing a Comparison function for Excel Visual Basic
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.
1 answer
Sort by: Most helpful
-
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