Reference with formats; how to reference text and fill color of a cell?

Hi everybody,

Please, can you help me with the following problem I have?

Cell C1 is blue text and a red background

What I can do is in cell A1 :

Which will set the text value of A1 to the color of the text in C1 (blue)

But I also want the background of C1 with that reference in A1.

So A1 will have a red background with blue text also.

Any idea how I also can copy the background format of C1 into A1?

Thank you so much for helping me with this.

Have a nice day.

Kind regards,


Re: Reference with formats; how to reference text and fill color

#12925 On 15 November, 2020 13:39 Thomas Kaegi said,

Hello Chris

The background of a cell cannot be referenced or copied, only its contents. I will provide a detailed instruction how to get around the problem. But you might read my recommendation in the very last paragraph before starting. It might make life much easier for you.

Here is what you can do:
- Select the container containing your spreadsheet and in the Format menu assign Fill Stye Sheet "Transparent"
- Select all cells (or at least the ones with which you want to work with a background) and in the Format menu assign Fill Stye Sheet "Transparent" (of course the red background in cell C1 will disappear)
- Without changing the selection: in the Spreadsheet menu select "Append Plane" (all of the contents of your spreadsheet will disappear - but don't be afraid – nothing is lost!)
- Select cell C1
– In the Spreadsheet menu select "Get info …" and in the table "Arrangement" set width and hight of the cell both to "Manual" and click "OK"
– In the Spreadsheet menu select "Contents Type" and assign "Drawing"
- Click inside the cell C1 - its selection will disappear as you have clicked in the drawing (not the spreadsheet cell as such!)
- At the very top click on the toolbox (= Outermost Container)
- With the rectangle tool draw a rectangle in cell C1 (it will actually be drawn in the drawing which is placed in that cell) not bothering about its dimensions
- Assign it the Fill Style Sheet "Red Fill"
- Assign it the Line Style Sheet "Shrill Line" (which in fact assigns the border the same Fill Style Sheet as to the contents, i.e. "Red Fill")
- In the Windows menu open the palette "Object Coordinates"
- Enter the following values for left border, top border, width and hight: depending on whether you are working in cm or inches: 0/0/1/1 for inches, 0/0/3/2 for cm
- Select the container containing your spreadsheet
- In the Edit menu select "Duplicate"
- In the contents-pulldown-menu of the toolbar change "Spreadsheet" to "No Contents" and acknowledge that the copy of the spreadsheet may be dismissed
- Open your document's inventory by clicking on the squirrel
- From the inventory drag your spreadsheet on the duplicated (now empty) container
- With the shift key pressed select the original container such that both containers containing your spreadsheet are selected)
- In the Windows menu open the palette "Drawing Commands" and click on the first alignment symbol (Align left and top sides)
You have now two containers lying on top of each other. The front one contains plane 1 of your spreadsheet, the other one plane 2 (with the red drawing)

The formula "#C1" in cell A1 (of plane 1) will only get the formatted text. There are two methods of getting the background as well.

Mathod 1:
- With cell A1 selected using the pull down menu or the respective command in the Spreadsheet menu change the plane shown in the front container to "2"
- Enter the same formula and switch back the planes

Method 2:
- No formulas in cell A1 of any of the spreadsheet planes!
- Anywhere in your document add the formula
SetCell(Spreadsheet 1!#$C$1;Spreadsheet 1!$A$1)&SetCell(Spreadsheet 1![2]$C$1;Spreadsheet 1![2]$A$1)
- If you choose to place it in a spreadsheet cell: put it in a cell which is outside the part of your spreadsheet visible in the container.
- If you choose the option "graphic text" it will produce a VALUE!-error because a text cannot contain a drawing. Never mind! The formula will do its job correctly. Just define the graphic text to be "non printable". I would not hide it completely as you may need to change the formula and would have a difficult time finding it.

I hope I didn't miss a step.

Well, all this is not really straightforward. I actually recommend another approach to working with backgrounds: Use the Power Function "MPFSetRangeBknd(Range;FillStyle)" with which you can control the fill style of a cell or range of cells with formulas. I highly recommend to buy the RT addition "Martin's Power Functions". This RT-extension contains other valuable functions not available in the basic program. It is contained in the Additions-folder of your basic RagTime installation package.

Kind regards, Tom

Re: Reference with formats; how to reference text and fill color

#12926 On 15 November, 2020 21:26 Bad_Wolf said,

Hello Tom,

Thank you again for your time and solutions to help me.

I tried your method without the use of the power functions and I got it to work in a blank test spreadsheet. It is indeed not very straightforward but it works after a few tries. So for the moment, I will use that function.

I do appreciate your help very much.

Wish you a very nice evening and all the best.

Kind Regards,