I am trying to make a small code which would search a provided range of numbers (which is selected with an input box) for codes from a 'dictionary'. All numbers are used as text, because dictionary numbers contain zeroes in front of them. I made small code based on the info I found on the net but it gives me an error 424 on of the lines of code (I will specify it in the code below)
Here is my workflow:
- Press the button to select the range of cells where the matches from the dictionary will be searched
- Convert the the selected range to Text
- Loop search the values from dictionary in the input-box-selected range and highlight the cells with the match.
- Press another button to reset formatting (to prepare the sheet for pasting another range).
Sub SearchAndFormat_Click() Dim Dictionary As Variant Dictionary = Range("O1:O671").value 'the dictionary range whose values to be loop searched Dim r As Range 'input box to select the range where the dictionary values will be searched Set r = Application.InputBox("Select range", "Selection Window", Type:=0) '424 error r.NumberFormat = "@" 'set input box values format to Text to avoid problems Dim word As Variant For Each word In Dictionary r.Find(word).Interior.ColorIndex = 4 'if dictionary values are found in r, add red fill to the cell Next End Sub
And here is the code for the clear formatting button in the previously selected range:
Sub ClearFormat_Click() r.ClearFormats End Sub
Looking forward to your support!