Excel conditional formating, how to duplicate rule without pasting

Hi,

I am using excell 2007 and am having a few problems with conditional formatting.
I am trying to do the following.
i have 6 cells in 6 columns ( c2, d2, e2, f2, g2, h2 ) each with figures 17.14+
Now i am trying to add a formula so when ever i insert a number in the cell below e.g c3 i want the cell c3 to turn green or red depending on the value in the above cell (c2).
so in all:

c2 has the value 17.14
if i insert the value 17.14 or less in cell c3 i want the c3 cell to turn green.
if i insert the value 17.15 or more i want the cell to turn red.
now i know how to do this in the conditional formatting feature but i want to do this will all the cells refering the the cell above.
i also do not want to create 6 diffrent conditional rules.

Is there a formula i can use that can be pasted into each cell and the cell number edited?

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Try selecting C3, D3, F3,G3, and H3 and then using Rule..

... use a formula to determine which cells to format.

then apply formula: =$C2<=17.4 ... green

add formula: =$C2>=17.5 ... red

See this link on understanding Conditional Formatting

Select Cell C3 to H3.

Go to the Home menu and click Conditional Formatting.

Select Manage Rules.

If you have any conditional formatting already applied to these cells, delete them.

Click New Rule

Select use a formula to determine which cells to format from the top section

Then enter the formula: =C2<=17.4

Click Format and choose Green from the Fill tab.

Click Ok.

Click New Rule and repeat above sith formula: =C2>=17.5

(sorry, the $ sign's should not have been there)

Click Format and choose Red from Fill tab.

Click Ok

Click Ok to Finish

When you do as prescribed above, the C2 will automatically update to D2, E2, F2, G2 and H2 for the corresponding cells in H2.

An alternative way, is just to do the formatting on C3 for cell C2, then copy that cell, select D3:H3 and then right-click, select Paste Special and select Formats.

Originally Posted by Immy15

hi,

sorry this is not what i am look for.
The option you have gave would format all cells to look at only 1 cell which is cell C2.
is there a way in which i can use if, then, else statements?

It is expected that suggestions given to you will be tested by you on your worksheet before you declare them non-working. This way you can see how the new stuff you're learning actually works.

Since you want to apply the CF by having cells look to the immediate cell above in the same column. . .

Select the entire range from C3 to the last cell in column-H, then apply the CF formulas NBVC gave you.

Excel will automatically update the row and column references for all of the cells in the selected range.