'Output list of unique red phrases to column C. 'Highlight any matches that appear at the end of the line Rng2HL.Cells(i).Characters(wordStart + 1, phraseLen).Font.ColorIndex = 3 If Not dictRed.Exists(tmpPhrase) Then dictRed.Add tmpPhrase, tmpPhrase If consec > 1 Then tmpPhrase = tmpPhrase & " " Set dictRed = CreateObject("Scripting.Dictionary") Sub highlightWords()ĭim rng2HL As Range, rngCheck As Range, dictWords As Object, dictRed As Objectĭim a() As Variant, b() As Variant, wordlist As Variant, wordStart As Long, phraseLen As Integerĭim re As Object, consec As Integer, tmpPhrase As String
Best way to compare two columns in excel code#
I also improved the highlighting code - I noticed that it would do weird things like only highlight the first instance of a non-matching word. If you want this list elsewhere, you'll have to adjust the address in the last section of the code. To compare the two cells, well start with a simple check, then try more complex comparisons. Since you added requirements in the comments below, I modified the code to also print out the list of red-highlighted phrases in column C. To concatenate the values, enter the following formula in D3 and copy it to the D4:D10: B3. Just be sure to change the addresses in the lines below to match your worksheet. The first step is to concatenate all the columns, so you’re not really comparing you’re counting. Rng2HL.Cells(i).Characters(wordStart, Len(wordlist(j))).Font.ColorIndex = 3 Select Duplicate from the left side box and then click on OK. Then go to Home> Conditional Formatting > Highlight Cells Rules > Duplicates Values. First, select the cells you want to compare.
'Check words one by one against dictionary. Using conditional formatting is the easiest way to compare two columns for a match.
'Reset range to highlight to all black font. If Not dictWords.Exists(wordlist(j)) Then 'Load unique words from second column into a dictionary for easy checkingįor j = LBound(wordlist) To UBound(wordlist) Set dictWords = CreateObject("Scripting.Dictionary") 'Change the addresses below to match your data. Select the entire dataset (except the headers) In the options that show up, click on New Rule In the Format values where this formula is true field, enter.Compare Two Columns (Side. Sub highlightWords()ĭim rng2HL As Range, rngCheck As Range, dictWords As Objectĭim a() As Variant, b() As Variant, wordlist As Variant, wordStart As Long Insert the following code into a VBA module.
0 kommentar(er)
