Excel formatting

  Craig.m 11:30 26 Feb 2003

Using Excel 97, or even 2000, I can get a cell format to change according to its content using conditional formatting. The problem I am having is getting another cell to mimic that formatting. So if a cells criteria make it highlight in red, I would like another cell to also adopt that format but conditional formating only seems to look at the cell you are in rather than another cell.

Is there a way to either, use an 'IF' statement and use a formatting statment, that would then be able to look at the contents of another cell or is there something simple that I am missing to do this?


  GroupFC 11:54 26 Feb 2003

I am new to all this computing business, but have been using Excel for some time (although I have never used conditional formatting!).

Is it not possible to use edit> copy on the original cell and then edit> paste special> format on the destination cell or will this not achieve the desired result?

  Craig.m 12:06 26 Feb 2003

That would be a good solution if it were only a few entries but I am trying to do a large spreadsheet and that would take quite a time to do with the potential for error as well. All I want it to do is either pick up another cells formatting (conditional) or be able to refer to another cell to decide what its format should be.


  emperor 12:54 26 Feb 2003

If you select a group of cells, and the one with the conditional formatting is in the top left corner of the group, then you should be able to select the conditional format of that cell and apply it to all the other highlighted cells.
If need be you can then de-select the formatting from those cells where it is not needed.
If your cells are a long way apart then you may need to highlight a very large area.
That is a simple way, but there is probably aquicker way picking up the formatting with the press of a button.

  emperor 12:57 26 Feb 2003

The quick way - select the cell with the conditional format.
On the toolbar, click the "format painter" button (the paintbrush next to the paste button on my setup) and then click on the cell where the format is required.

  emperor 13:13 26 Feb 2003

I've looked a bit more, and if the cell with the conditional format is anywhere in the higlighted group then the conditional formatting can be applied to all the cells in the group, it doesn't need to be at the top left.
PS all this is on Excel 97

  Paranoid Android 13:15 26 Feb 2003

Use Formula Is instead of Cell Value Is :

This is easiest to explain using an example. Assuming the conditional format is set in column C, for values greater than 1000 set format to red ;

Assuming you want column D to mirror this format;

Click a cell in column D (eg D2), select format, conditional formatting

In the Condition 1 box, select Formula Is

In the next box, type =C>1000 (or whatever the criteria is from column C)

In the format box, select red text or whatever.

Click OK.

You can then copy this format down the rest of the column using copy - paste special - formats.


  Craig.m 14:21 26 Feb 2003

I think I did not clarify things - I am fine at using the same conditional formatting and the pasting of it around but that conditional formatting acts on the value in the cell it is pasted to.

What I want to do is if a cell on one part of the sheet is conditionally formatted according to its content, I want another cell in another cell on the sheet to have the same format as the first but according to the firsts content.

For example - if a cell is red for no and green for yes say A1, I then want cell ZZ1 to change to the same format as the A1, its content is discountable, I just want it to adopt the same format as A1 but not the same Conditional format statement as it will not react in the same way as its contents could be totally different data. I am just trying to give a sheet more readability by having various area of the sheet change colour according to the criteria in another area.

  emperor 14:34 26 Feb 2003

Sorry for misunderstanding your request.
I had a vague feeling that I might not be answering the real question. However, I cannot answer the real one - I have been wanting a way of relatively referring to other cells for ages, and not found one. I will follow the rest of the posts on this with interest.

  cherria 15:41 26 Feb 2003

You need to do what Paranoid Android told you to do.

In your example, in cell ZZ1 set the conditional formatting to be :
Formula is then set the value to be =A1="yes" and set the format to green then add another and make it formula is =A1="no" and set the format to green.

MAke sure that you have recalculation set to automatic (Tools-Options-Calculation) then when you change A1 to yes, the format of ZZ1 will go red etc.

  emperor 16:27 26 Feb 2003

I have not come across your suggestion before - I have tried it and I like it. Thanks.
It doesn't do all I want, but it is good, and as this is not my thread anyway it doesn't matter that I still have some more searching to do

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires