Checking and filling Excel cells

Question

Checking and filling Excel cells

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

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

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

excel vba excel-vba

asked by anonymous 05.09.2017 / 21:45

2 answers

How to Migrate PosgreSQL Script to Mysql Script? How to change a table indicating that the data of a certain field can only vary from date 1 to date 2?

score 1 · Answer 1

Hello

I think you do not need VBA for this, you can use conditional formatting as follows:

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

Select the last Use a formula ... option, as shown below, enter the formula below and click the Format ... button and format accordingly wish

Formula:

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

[IMPORTANT]ThepartoftheB5formulawillbeyourfirstselectedcell,notethatinmyexampleimageitisincolumnBinrow5.Andremovethe$sign.

Thepartoftheformula$D$20:$D$22iswhereyouhavetheverificationdata(yoursecondimage).

EDITION1

Followtheimagebelowtoformattostayasyousuggestedinthequestion.ThescreenbelowwillappearafterclickingFormat...,clickFillandchoosefillcolor:

I hope I have helped!

score 1 · Answer 2

Assuming the search data is in column C of Worksheet (1) and you will look in Worksheet (2), this is a 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 to use an Object Dictionary, it is faster and optimizes processing time. Recommended for spreadsheets with lots of data.