Copy Conditional Formatting to other rows

**Regan_Mitchell** · 01-15-2010, 02:33 AM

Hey Guys
I'm running a spreadsheet at the moment in 2007, and one of the funcitons I've made it for certain cells to either change format if the number is to big or to small depending on the cell it is.

I have formatted all for one line against duplicate scores on the same line.
The problem is this is time consuming and there are 22 different lines.
Because with Conditional Formating you can't simply drag like you can with formulas, I was wondering if there was a more simple approach. I have demonstrated the formating required on Row 6, and require the same formating on rows 8:10, 14:26, 32:36.
Also, just to make things complicated, the values change for each line.
I have attatched the file. Row 6 with the label DEMO LINE contains all the correct formating I require. I hope one of you can help me out.
Regards,
Regan

**NBVC** · 01-15-2010, 09:16 AM

If you remove the $ from before the row number (6) in each of those conditional format conditions in row 6, then you can simply select one of the cells in row 6 with the conditional format applied, select the cells in the same column and go to Edit|Paste Special and select Formats...

Repeat for each column with formatting.

The formatting auto updates to the relative row number.

**Regan_Mitchell** · 01-20-2010, 07:44 AM

I have some bad news. Unfortunately this method didnt work...

**NBVC** · 01-20-2010, 08:47 AM

In what way didn't it work for you?.. I tried it on your sample and was fine.

**zoktolk** · 10-14-2013, 04:11 PM

Hi!

I have a similar issue where even by removing the $ from before the cell number I cannot copy the conditioning correctly.
I am sure the mistake is mine but can you please assist and let me know where I go wrong?

I have a sheet with about 10 columns. In the last cell of each row that is J2 for the 1st row I have the following conditional formatting.

Use a formula to determine which cells to format.
Format values where this formula is true:
=$J$2="Done"

formar all cells in this row (A2-J2) to be filled as green.

Applies to: =$A$2:$J$2

Column J is formatted so I can select 4 values from a drop down list that is from sheet 2.

When I try to use the Format Painter all it does is fill the cell J3 with the appropriate colour but not row 3 as I would like to.

So when I select Done in J2 all the second row goes green. However, when I copy this to J3 all I get is the same colour as in J2 and if I choose an other value form the drop down list not even the cell changes colour. But if I change value in the cell from which I copied the cell info, the cell where I copied to also changes colour.

If I update J3 to =$J$3="Done" and the appleies to field to =$A$3:$J$3, then the formatting is working fine.

How can I automate the use of this formatting to all the other cells in column J?

Thank you.

**arlu1201** · 10-15-2013, 08:19 AM

zoktolk,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.