Checking and Filling Excel Cells

Question

I have the following spreadsheet as image below, I would like to create a VBA to check if the content inserted in the range of cells of this spreadsheet exists in the list of the second image below, if it exists, fill in green the cell of the contents of the image 1.

NOTE: I did not use conditional formatting because the added numbers are always dragged to other cells in some document revisions.

Answer 1

Hello!

I think you don’t need VBA for this, you can use condicinal formatting as follows:

1. Select all cells you want to color, go to conditional formatting and click New Rule

2. Select the last option Use smokes formula...., as shown below, insert the formula below and click on the button Format... and do the formatting as you wish

Formula:

=SE(ÉERROS(PROCV(B5;$D$20:$D$22;1;FALSO));FALSO;VERDADEIRO)

[IMPORTANT] The part of the formula B5 will be your first selected cell, note that in my sample image is in column B on line 5. And remove the dollar sign $.

The part of the formula $D$20:$D$22 is where you have the verification data (your second image).

EDITION 1

Following image below is how to format to look the way you suggested in the question. The screen below will appear after clicking on Format..., click Fill and choose the fill color:

I hope I’ve helped!

Answer 2

Assuming the search data is in the C column of the Sheet(1) and You will search in the Sheet(2), this is code with the function . Find

Dim Rng As Range, rng2 As Range

ncell = Sheets(1).Cells(Sheets(1).Rows.Count, 3).End(xlUp).Row

    Set Rng = Sheets(2).Cells 'range para procurar
    Set rng2 = Rng(1, 1)

    For j = 1 To ncell
    pesquisar = Sheets(1).Cells(j, 3).Value 'referência de procura na coluna 3 (ou seja, C)
        With Rng
        Set cellFound = .Find(what:=pesquisar, After:=rng2, LookIn:=xlValues)
            If Not cellFound Is Nothing Then
                FirstAddress = cellFound.Address
                Do
                    Sheets(2).Range(cellFound.Address).Interior.ColorIndex = 4
                    Set cellFound = .FindNext(cellFound)
                Loop While Not cellFound Is Nothing And cellFound.Address <> FirstAddress
             End If
        End With
    Next

End Sub

Another alternative is the use of an Object Dictionary, this is faster and optimizes the processing time. Recommended for spreadsheets with lots of data.