Copy Conditional Formating (Excel 2011)

[SkaFreaks]

Hello,
I've been researching my problem but can not get it to work.
Here is the setup, 5 columns (A-E) 20 rows (1-20)the conditional formating is set so if A1=0 then it will fill A1-E1 black.I want to copy it down so each row works the same. However every time I copy and then special paste formulas it will copy it but always refer back to if A1=0 and not B1,C1 and so on.Is there a way to do this without having to edit the formatting for each row?

Thank you for any help
SkaFreaks

 

[grizz]

I use 2003 but it may work -- highlight row 1 and conditional format / formula is / =INDIRECT("A"&ROW())=0 and format your colour
now click row 1 -- copy --- highlight down as far as required and paste special / click formats and ok

 

[SkaFreaks]

I tried the suggestion and it didn't seem to work. However for your formula am I entering it exactly as written? ie =INDIRECT("A"&ROW())=0 or should ROW() equal the actual row number or something else?
Thanks!

 

[grizz]

I hope I understand you correctly -- if A1=0 then colour A1:C1
if B2=0 then colour B2:C2 -- if A3=0 then colour A3:C3
if this is correct highlight A1 : C1 and use the formula just as it is in conditional format and copy down

 

[SkaFreaks]

Awesome! Thank you for the help.
I had two problems with the original solution. 1. When I entered it into my actual spreadsheet I had the column in B not in A but still entered A in the formula. 2. The conditional format was only applying to the C column, so I adjusted the Applies To to all the columns and it all works now.
Thank you again!