Copying cell background format in formula

[sharshra]

I have few cells color coded in the source table. When these cells are referred in another table, I would like to get the same background format. Is that possible? Can the experts advise please?

Example - Few cells are color coded in the source table. When I refer these in the new table, it should get the same background color as that of source table.

Source table:

excel problems.xlsx
	P
21	3
22	d3
23	9
24	2
25	54
26	7n
Sheet2

New table:

excel problems.xlsx
	R
21	54
22	9
23	2
24	7n
25	ab
Sheet2

 

[6StringJazzer]

Let me be as clear as possible about my question.

To provide VBA code to solve this, I need to know exactly which cells are in the first range, and exactly which cells are in the second range. Your example shows that the first range is P21:P26, but you need to give us the full range of cells in the source table. Same for the table where you will be entering data.

 

[Peter_SSs]

I have no control on the conditional format done in the source file.

Earlier you said

First table has no conditional formatting.

I will assume no conditional formatting, just formatting.

As Jeff has pointed out you still have not detailed just what cells are (or might be) in either range. I have assumed the first range is the whole of (or anywhere in) column P and the second range is the whole of (or anywhere in) column R.

This is a worksheet 'event' code so to implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by entering/deleting/editing values in column R, given the original values/colours are already in column P.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, rFound As Range
  
  Set Changed = Intersect(Target, Columns("R"))
  If Not Changed Is Nothing Then
    For Each c In Changed
      Set rFound = Columns("P").Find(What:=c.Value, LookAt:=xlWhole, MatchCase:=True)
      If rFound Is Nothing Then
        c.Interior.Color = xlNone
      Else
        c.Interior.Color = IIf(rFound.Interior.Color = 16777215, xlNone, rFound.Interior.Color)
      End If
    Next c
  End If
End Sub

 

[sharshra]

Thank you very much, @Peter_SSs Your code is of great help I can make some tweaks to make it suitable for the actual table.

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.

 

[Dave Patton]

I do not know the criteria that you use to create the new table.

You may want to look at Data | Sort & Filter | Advanced

I used the built in feature of Excel and moved a portion of the range to a new range.

T202501a.xlsm
	P	Q	R
18
19
20
21	3		9
22	d3		2
23	9		54
24	2		7n
25	54
26	7n
27
28
6c