The script provided below was supposed to compare planned transfers with those actually executed, fetched from online banking. The goal was to detect any mistakes, duplicates, and transfers that were planned but not executed. Preparing it with my new buddy, ChatGPT, took me about three hours. The result – it works exactly as I wanted, minimizing the likelihood of missing costly errors. Manually checking the transfers, which is a tedious and repetitive task, used to take me about 2-3 hours (during which I fell asleep several times :)). So, the gain is more than measurable.
Original query: I need a script for searching strings in Excel. Search the range column P to the row containing the string STOP in Arkusz1, compare column T to the row with the keyword STOP in Arkusz2. Highlight non-empty cells in Arkusz1 column P, whose values don’t have equivalents in the range column T Arkusz2, and if in the appropriate row in Arkusz1 column M there isn’t the keyword O up to the row containing the string STOP in Arkusz2. Ignore empty cell values in Arkusz1 Column P and ignore empty cells in Arkusz 2 column T.
Of course, during the refining of the script, modifications to this query were made. I tried not to write in a style like “but instead of column T, I need column P”, but to make corrections to the original query and ask the same “already corrected” question again. In the case of simple tasks, this is more effective.
Result:
Below is the VBA script that should meet your requirements:
Sub FindAndHighlight()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim cell1 As Range
Dim cell2 As Range
Dim flag As Boolean
Dim stopRow1 As Integer
Dim stopRow2 As Integer
‘Ustalamy arkusze
Set ws1 = ThisWorkbook.Sheets(“Arkusz1”)
Set ws2 = ThisWorkbook.Sheets(“Arkusz2”)
‘Znajdujemy wiersz ze słowem kluczowym STOP w Arkusz1 i Arkusz2
For Each cell1 In ws1.Columns(“P”).Cells
If cell1.Value = “STOP” Then
stopRow1 = cell1.Row
Exit For
End If
Next cell1
For Each cell2 In ws2.Columns(“T”).Cells
If cell2.Value = “STOP” Then
stopRow2 = cell2.Row
Exit For
End If
Next cell2
‘Ustalamy zakres
Set rng1 = ws1.Range(“P1:P” & stopRow1)
Set rng2 = ws2.Range(“T1:T” & stopRow2)
‘Porównujemy wartości
For Each cell1 In rng1
If cell1.Value <> “” And ws1.Range(“M” & cell1.Row).Value <> “O” Then
flag = False
For Each cell2 In rng2
If cell1.Value = cell2.Value Then
flag = True
Exit For
End If
Next cell2
If flag = False Then
cell1.Interior.Color = RGB(255, 255, 0) ‘Podświetlenie na żółto
End If
End If
Next cell1
For Each cell2 In rng2
If cell2.Value <> “” Then
flag = False
For Each cell1 In rng1
If cell2.Value = cell1.Value Then
flag = True
Exit For
End If
Next cell1
If flag = True Then
ws2.Rows(cell2.Row).EntireRow.Interior.Color = RGB(255, 255, 0) ‘Podświetlenie całego wiersza na żółto
End If
End If
Next cell2
End Sub
This script starts by finding the row containing the word “STOP” in columns “P” of Arkusz1 and “T” of Arkusz2. It then compares the values within these ranges. If a cell in column “P” of Arkusz1 does not have a match in column “T” of Arkusz2, and if the corresponding row in Arkusz1 column “M” does not contain the word “O”, then such a cell is highlighted in yellow. Similarly, if a cell in column “T” of Arkusz2 has a match in column “P” of Arkusz1, the entire row containing that cell is highlighted in yellow.
