Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Inscreva-se  \/ 
x

or

Aviso

JUser:: _load: Não foi possível carregar usuário com ID: 20319

Como definir a cor da célula igual a outra cor de célula no Excel?

Se você quiser fazer a correspondência de uma cor de célula com outra, um método neste artigo pode ajudá-lo.

Definir a cor da célula igual a outra cor de célula com código VBA


Definir a cor da célula igual a outra cor de célula com código VBA


O método VBA abaixo pode ajudá-lo a definir uma cor de célula igual a outra no Excel. Faça o seguinte.

1. Na planilha, você precisa combinar a cor de duas células, clique com o botão direito na guia da planilha e clique em Ver código no menu do botão direito. Veja a imagem:

2. Na abertura Microsoft Visual Basic para Aplicações janela, você precisa copiar e colar o código VBA na janela Código.

Código VBA: definir a cor da célula igual a outra cor de célula

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Range("C1").Interior.Color = Me.Range("A1").Interior.Color
End Sub

Importante: No código, A1 é a célula que contém a cor de preenchimento que você combinará com C1. Altere-os de acordo com suas necessidades.

Em seguida, a célula C1 é preenchida com a mesma cor da célula A1, conforme mostrado na imagem abaixo.

A partir de agora, quando a cor de preenchimento em A1 for alterada, C1 será correspondido com a mesma cor automaticamente.


Artigos relacionados:


As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Super Formula Bar (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    taitran07 · 3 months ago
    Hi -- I'm having a difficult time using your code. I'm trying to do exactly what you're saying. Make one cell be the same color as another cell (without any values necessary) on the same sheet. Is there something in that code that should be adjusted?
  • To post as a guest, your comment is unpublished.
    · 2 years ago
    I've attached a screen shot of the file Im working on - I don't seem to be able to upload a .xlsm file? In this file I have used the original VBA from this thread and can now match the conditionally formatted colour of cell A10 in D10. How can I get this to work on a range of cells? I would like to get the colour of the range of cells A10:A200 to transfer over to D10:D200. Can anyone help please? There is a drop down list in use in the A column but once we have placed an order we need to be able to over type the purchase order number. The items in the list are all set to conditionally format to a colour (eg ORDER OK turns cell green, CLIENT TBC turns cell yellow) but the colour goes blank once the PO has been put in.
    • To post as a guest, your comment is unpublished.
      · 2 years ago
      image didn't upload - hopefully attached now....
  • To post as a guest, your comment is unpublished.
    · 2 years ago
    Wow - this is great. I hope you can help me adapt your script to my needs. I need to make cells D10:D200 match the conditionally formatted colour of cells A10:A200 - can you help me to get this working please. The cells are all in the same worksheet.
  • To post as a guest, your comment is unpublished.
    Tony · 2 years ago
    As Chris I am interested in copying the background color from another worksheet?
  • To post as a guest, your comment is unpublished.
    Chris · 2 years ago
    I found your code to 'set cell color to equal to another cell color" and it works when I am using it on the same worksheet ( ex from cell A1 to cell A2). I am wondering if there is a way to have this same functionality from another worksheet (ex to copy cell color from sheet1!A1 to sheet2!A1? Any help you could offer would be appreciated!
  • To post as a guest, your comment is unpublished.
    Zack · 3 years ago
    This is a good start to what I am looking to do. But I am looking for something a bit more complicated

    How could I adapt this to apply to multiple rows and a range. For instance I have a header column in Column B, I want cells from G to CS to match the colour of the header row but only is they have something in them ie the letter x. I know I can write an IF and THEN statement but how would I apply it to multiple Rows without writing a code for each row.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Zack,
      Sorry can't help you with that. Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
  • To post as a guest, your comment is unpublished.
    yesser · 3 years ago
    hi , how to apply the same VBA but on a range of cells for example :

    i want to have the same color of range (C8:X8) to be apply on the range (S16:AL16) one by one in the same order (S16 get the color of C16 , T16 get the color of D8 ....etc)
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good day,

      The below VBA code can help you solving the problem. Thanks for your comment.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim xSRg, xDRg, xISRg, xIDRg As Range
      Dim xFNum As Long
      On Error Resume Next
      Set xSRg = Range("C8:X8")
      Set xDRg = Range("S16:AL16")
      For xFNum = 1 To xSRg.count
      Set xISRg = xSRg.Item(xFNum)
      Set xIDRg = xDRg.Item(xFNum)
      xIDRg.Interior.Color = xISRg.Interior.Color
      Next xFNum
      End Sub
      • To post as a guest, your comment is unpublished.
        Shaun Symmonds · 2 years ago
        Hi

        I am trying to do similar, but I have two spreadsheets (files). Spreadsheet 1 is the Master where the data is manually updated and file (spreadsheet 2) is equalling the data in the same cell as spreadsheet 1. When I open spreadsheet 2, I get a prompt to refresh with spreadsheet 1 no promlems, but if the colour of the cell is changed in spreadsheet 1 it does not update in spreadsheet 2, neither does 'strike-trough' of fonts..help please?
  • To post as a guest, your comment is unpublished.
    JJ · 3 years ago
    Hi, the cell being referenced for colour changes colour based on conditional formatting. The above doesn't seem to work with that and the destination cells are staying blank. How can this be corrected? Thanks
    • To post as a guest, your comment is unpublished.
      LP · 3 years ago
      If you have Excel 2010 or later you can use the DisplayFormat function to return the color of a conditionally formatted cell. See below:

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Me.Range("C1").Interior.Color = Me.Range("A1").DisplayFormat.Interior.Color
      End Sub
      • To post as a guest, your comment is unpublished.
        Rutger · 1 months ago
        Hi, would it be possible to extend this to a conditional formatting; not to match a color created by conditional format, but if a conditional format condition is matched, that the applied conditional format takes on the color of a specific cell. Trying to use this in a gantt chart, colouring the days between start and end date, but the conditional format that generates the gantt bars, should take the color of the cell that contains the Task (which I set manually)
      • To post as a guest, your comment is unpublished.
        ISAM QASHASH · 6 months ago
        can this be done on range of cells or just for a single cell?
      • To post as a guest, your comment is unpublished.
        Jose De Jesus · 1 years ago
        LP you are absolutely AMAZING!!!!!!
        i was trying for the longest time to get cells to match the conditional formatting background color! you are a lifesaver!!!
    • To post as a guest, your comment is unpublished.
      Marty · 3 years ago
      I have the same problem. Works on cells without conditional formatting but doesn't with those that do