Dragging Conditional Formatting

[avido83]

Hello,
Is there a way to drag conditional formatting and have the references change as you drag? I would like C6 to highlight red if both C6 and T6 equal a referenced cell and T6 is not empty. I can do this by writting a rule in conditional formatting that says:

=AND(C6=K4,T6=K4,T6<>"")

I would like to be able to drag the rule so that do not have to update the references for each cell I want formatted. Does this make sense?

 

[avido83]

UPDATE:
I realized that I have two different instances of the same problem and I used the more complex one in my first example. The other example is that I would like to highlight any cell on sheet1 that matches the corresponding cell on sheet2.

=AND($A$1=Sheet2!$A$1,Sheet2!$A$2<>"")


But again my problem is applying the rule to the a larger area without having to enter the rule for each cell in a larger area.

Thanks for any help you have to offer!

 

[ukmikeb]

Hi

Select the area that you want the rule to apply to before setting the rule and certainly for Excel 2007 and later the rule will propagate automatically.

Again for 2007 and later you have the Format Painter facility where you can copy the conditional formatting to other cells.

For the second case ensure you don't specify absolute references.

hth

 

[ukmikeb]

Duplicate post - deleted.

 

[avido83]

Thanks Mike! I knew it was something simple, those absolute references do it every time! When you are in the CF area and select cells Excel automatically makes it an absolute reference. But thanks to your help, problem solved!

 

[ukmikeb]

Hi

Thanks for the feedback.

Pleased to have helped solve your problem.

Good luck with your project.