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.
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.
- Como tornar o nome da guia da planilha igual ao valor da célula no Excel?
- Como alterar o valor com base na cor da célula no Excel?
- Como mudar a cor da célula quando a célula é clicada ou selecionada no Excel?
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 months agoHi -- 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 agoI'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 agoimage didn't upload - hopefully attached now....
- To post as a guest, your comment is unpublished.· 2 years agoWow - 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.· 2 years agoAs Chris I am interested in copying the background color from another worksheet?
- To post as a guest, your comment is unpublished.· 2 years agoI 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.· 3 years agoThis 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.· 2 years agoHi 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.· 3 years agohi , 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.· 3 years agoGood 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
- To post as a guest, your comment is unpublished.· 2 years agoHi
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.· 3 years agoHi, 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.· 3 years agoIf 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
- To post as a guest, your comment is unpublished.· 1 months agoHi, 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.· 6 months agocan this be done on range of cells or just for a single cell?
- To post as a guest, your comment is unpublished.· 1 years agoLP 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.· 3 years agoI have the same problem. Works on cells without conditional formatting but doesn't with those that do