Applying conditional formatting to multiple cells in a Column

**SizeXL** · 01-25-2010, 09:50 AM

For the life of me I haven't been able to figure out a proper way to do this besides manually updating each cell. This seems like such a simple task but hard to figure out. After 1 year of manually doing it I decided to ask for help and see if there is a faster way to do this because I will be doing this for more years to come lol. So here is my test excel sheet:

A1 Value = "167"
B1 Value = "0"

I click on B2 and click on Conditional Formatting and set it up so that [IF B1 value =A1 value then B1 bgcolour is Green]. Simple right? Yes.

Heres, where it gets tricky for me...

How do I copy and paste this conditional formatting so that I can continue this pattern so that [IF B2 value = A2 then bgcolour is Green] ?

If you tell me to click on the fill button and drag down, it won't work because Excel will screw up the conditional formatting so that
[IF B2 value is A1 then bgcolour is Green]
[IF B3 value is A1 then bgcolour is Green]
[IF B4 value is A1 then bgcolour is Green]
[IF B5 value is A1 then bgcolour is Green]

The conditional formatting does not want to increase A1 to A2, A3, A4, A5 etc.

What is the best way to copy and paste my conditional formatting formula so that the conditional formatted cell checks the cell to its left to verify data?

I have included a visual example of an attached XLS file to make it easier for you to understand and try out yourself.
Thank you!

**Ron Coderre** · 01-25-2010, 10:14 AM

In Excel, dollar signs ($) lock the part of the reference they precede.
examples:
=$A$1....when copied anywhere, will ALWAYS refer to cell A1
=$A1....when copied anywhere, will ALWAYS refer to Col_A
=A$1....when copied anywhere, will ALWAYS refer to Row_1

so...Change the formula in your conditional formatting to remove the dollar signs.
Example:
•Select B1:B3, with B1 as the active cell
•Format.Conditional_Formatting
•••Formula is: =B1=A1
•••Click the Format button and set the fill color
•••Click: Ok

Note: if the CF formula was set to =B1=$A$1...then every cell would be compared to A1.

Does that help?

**georgeanaprop** · 01-25-2010, 10:50 AM

Try creating the conditional format in Cell B1

Then autofill all the way down. Once autofilled, click the little box at the end (it has some cells and a small "+" sign in it - it should write "Autofill Options" and select Fill Format Only)

It should copy the conditional formats for you

or else you can copy cell b1 - paste - paste special - formats all the way down column B

**SizeXL** · 01-26-2010, 08:36 PM

Wow thanks so much! I always overlooked the dollar signs because my cells were formatted for currency and figured it was linked to the type of cell and not a symbol for locking. Both solutions work very well
Cheers!

I hope this post can help others with the same situation!