Conditional completion

Asked

Viewed 157 times

0

I need a help with VBA.

My question is this::

I need cell "B" and "C" to receive a specific value when I type a value into cell "A":

For example: When I type the value "1" in cell "A", cell "B" should receive 'MOTO' and cell "C" should receive 'CAR'

When I type the value "2" in cell "A", Cell "B" should receive 'HOME' and Cell "C" should receive 'APARTMENT'

When I type the value "3" in cell "A", Cell "B" should receive 'DOG' and Cell "C" should receive 'CAT'

As if the numbers typed in cell "A" were codes that alter the behavior of the following cells!

Edit_1: there will be times when cell B and C will receive different values (from the keyboard) from DOG and CAT, so it needs to be a function or code that behaves like conditional formatting

Thanks in advance!

excel vba excel-vba conditional-formatting

  • I don’t know if I understand but maybe the PROCV (or PROCH) function can help you.

  • In fact, I imagine how to use PROCV or H... the idea is that this information exists only in the formula or in VBA, its functionality is exactly the same as the conditional formatting, only instead of painting the cell or the border, it fills the cell with a predefined text.

1 answer

1


Double click on Planilha1 (Planilha1), as in the image. You can change, in your case, but just do this procedure in the spreadsheet where you want the code to be executed.

inserir a descrição da imagem aqui

Put the following code:

 Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Target.Worksheet.Range("A1")) Is Nothing Then
                Select Case Range("A1").Value
                    Case 1
                        Range("B1").Value = "MOTO"
                        Range("C1").Value = "CARRO"
                    Case 2
                        Range("B1").Value = "CASA"
                        Range("C1").Value = "APARTAMENTO"
                    Case 3
                        Range("B1").Value = "CACHORRO"
                        Range("C1").Value = "GATO"
                    Case Else
                        Range("B1").Value = ""
                        Range("C1").Value = ""
                End Select
        End If
    End Sub

  • I apologize Paulo, I forgot to inform that there will be times when cell B and C will receive different values (from the keyboard) of DOG and CAT, so it needs to be a function or code that behaves as the conditional formatting understands? Thank you so much for your help and I will edit the question. .

  • Fine then I’ll change the code, wait.

  • @Christiano.Santos code changed.

  • 1

    The code works perfectly, thank you very much for the help @Paulo Vieira. .

Browser other questions tagged excel vba excel-vba conditional-formatting

You are not signed in. Login or sign up in order to post.