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]

There are no built-in functions that retrieve cell fill color (or any other cell formatting). This can probably be done with VBA but the actual code depends entirely on a lot detail about what you are doing. What do you mean by "when I refer to these"? You are not showing any formulas.

 

[sharshra]

@6StringJazzer, thanks for the response. What I mean is - when I enter a number / character in the new table, if it matches with the source table, it should get the same background format (color, text style etc). I understand it is possible with VBA, but I was looking for getting the same result without using VBA.

 

[Peter_SSs]

I was looking for getting the same result without using VBA.

The only way that would be possible is if the formatting in the first table is applied through Conditional Formatting and it is possible to apply the same Conditional Formatting to the second table.

 

[sharshra]

First table has no conditional formatting. It is received from a different source & I have no control on the formatting made. 2nd table also has no conditional formatting except that it should mimic the first table when numbers / characters in the 2nd table match the 1st table.

 

[6StringJazzer]

In that case VBA is the only way.

To give you code that will actually work for your file I need the details about where is the first table (I assume it's really more than P21:P26), where is the second table. Are these just ranges, or are they structured tables? You didn't show much context.

 

[Peter_SSs]

If using vba is going to be acceptable, we would also want to know if it is possible that in the first table the same value could occur more than once with different colours and, if so, how that should be handled in the second table.

 

[sharshra]

Thanks, @6StringJazzer & @Peter_SSs. You are right, I have shown the example for illustration only. They are in ranges. Not in tables.

It´s an interesting question about duplicate values having different format. I haven´t thought about it. So far, I haven´t come across any duplicates, but it is very much possible.

First, I would like to tackle the problem assuming that there will be no duplicates. As a second step, I have to think about handling the duplicates.

 

[Peter_SSs]

Thanks, @6StringJazzer & @Peter_SSs. You are right, I have shown the example for illustration only. They are in ranges. Not in tables.

It´s an interesting question about duplicate values having different format. I haven´t thought about it. So far, I haven´t come across any duplicates, but it is very much possible.

First, I would like to tackle the problem assuming that there will be no duplicates. As a second step, I have to think about handling the duplicates.

Unfortunately that did not address this point raised by @6StringJazzer :

I need the details about where is the first table (I assume it's really more than P21:P26), where is the second table.

A further question: Are the values in the second range the result of formulas or are they being entered manually?

 

[sharshra]

I get the file from an external party. As mentioned before, I have no control on the conditional format done in the source file. And there is not much to be done with the source file. It is used only for reference. I hope this clarifies.

Values in the 2nd range is entered manually. When entered, if it matches with the values in the source file, it should mimic the format (background color, text style) of the source file.