How to copy cell colour defined by conditional formatting to another cell

Question

Please help me, as I am not very experienced with VBA.

Let's say I have the following cells with specific background colour as defined by conditional formatting. (Cells A3:A6)

enter image description here

Now I would like cells (C3:C6) to copy the colour from A3:A6 and apply it to C3:C6.

enter image description here

Please help me get a code that will recognize the colour set from Conditional formatting and change C3:C6 respectively. Thank you very much.

EDIT:I messed up and didn't enter any values for cells so it may be confusing how is there conditional formatting when nothing is in the cell. Assume cells with colours are 1-5.

Current code:

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 = Sheet1.Range("A3:A5")
Set xDRg = Sheet1.Range("C3:C6")
For xFNum = 1 To xSRg.Count
Set xISRg = xSRg.Item(xFNum)
Set xIDRg = xDRg.Item(xFNum)
xIDRg.DisplayFormat.Interior.Color = xISRg.DisplayFormat.Interior.Color
Next xFNum
End Sub

Search for VBA code to get the color of a cell and then to apply that color to another cell. There are lots of examples on the interwebz that can get you started. We're happy to answer questions about your specific code to help solve problems along the way. — PeterT, Jul 25 '22 at 11:58
Does this answer your question? [How to get the background color from a Conditional Formatting in Excel using VBA](https://stackoverflow.com/questions/45122782/how-to-get-the-background-color-from-a-conditional-formatting-in-excel-using-vba) — Ike, Jul 25 '22 at 12:01
Please see the latest edit to the post. This is what I currently have. — DM24, Jul 25 '22 at 12:02
So the code above works if I manually select a colour for the cell. However, if the colour is defined by Conditional Formatting, it does not work. — DM24, Jul 25 '22 at 12:07
I was recommended to use .DispalyFormat.Interior.Color so I have adjusted my code to have xIDRg.DisplayFormat.Interior.Color = xISRg.DisplayFormat.Interior.Color instead of xIDRg.Interior.Color = xISRg.Interior.Color — DM24, Jul 25 '22 at 12:09
Just saw that you have asked the same question already (https://stackoverflow.com/q/73082126/16578424) - this no good SO behaviour :-/ — Ike, Jul 25 '22 at 12:52
Can't you just use another conditional formating for column C based on the vaues of column A? — MGP, Jul 25 '22 at 12:53
First step in debugging is to comment out `On Error Resume Next` — Tim Williams, Jul 25 '22 at 16:05

score 0 · Answer 1 · answered Jul 25 '22 at 12:49

To be honest - your question/your requirement doesn't make any sense in my eyes (esp. within the selection change event and w/o using target) - but here is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Sheet1
    Dim rgFormatCondition As Range
    Set rgFormatCondition = Me.Range("A3:A6")
    
    Dim rgTarget As Range
    Set rgTarget = Me.Range("C3:C6")
    
    Dim i As Long
    For i = 1 To rgFormatCondition.Rows.Count
        rgTarget(i).Interior.color = rgFormatCondition(i).DisplayFormat.Interior.color
    Next
End With
End Sub

You have to apply the displayformat-color of the cells with format condition to the interior color of the target cell.

How to copy cell colour defined by conditional formatting to another cell

1 Answers1