Conditional Formatting not reading each row

[REHABGUY1960]

I have a spreadsheet with targets for each month in one row and another row where I count how many I have. The targets vary by month. I want the cell that counts number achieved to turn green when it is equal to or greater than the target. I can do that one cell at a time, but when I try format painter or copy/paste special format, the whole column turns green based on the top row.

Please, help.

 

[Joe4]

I can do that one cell at a time, but when I try format painter or copy/paste special format, the whole column turns green based on the top row.

Sounds like you may have used some absolute range references in your formulas (you have $ signs in your ranges). If you want your ranges to "float" as you move down a column or across a row, you do not wnat to use those. See this here: http://www.cpearson.com/excel/relative.aspx

Personally, I do not like to use the Format Painter. I like to select the whole range that I want to apply the Conditional Formatting to, and then write the Conditional Formatting formula as it applies to the very first cell in my range. If you use the correct fix of absolute/range references, it will work properly.

If you need more help getting it to work, please provide the specific details (what range you are applying to, what you are entering, etc).

 

[REHABGUY1960]

Thank you that fixed it. For some reason, that seems counter intuitive to me, like it should be the other way around.

Thanks again.

 

[Joe4]

For some reason, that seems counter intuitive to me, like it should be the other way around.

What does?

If you let me know exactly what you are referring to, I can probably explain it.

 

[REHABGUY1960]

Your suggestion worked perfectly. In my mind, it seems the $sign should mean relative. Just the cell address should mean just the cell address. If there is logic behind that, it might help me flip it in my head.

Thanks.

 

[Joe4]

It is more common than not that you DON'T want to lock the cell references (think of Auto-Fill or Copy down functionality).
Most formulas are access other columns in the same row. So if you copied a formula down from row 2 to row 3, you would want the new formula to reference the columns in row 3 (not in row 2).
So the "default" (to allow it to float), is without the "$" sign.

The "$" locks whatever range reference comes right after it (row or column). The use of that is less prevalent (than "free-floating) , so I appreciate that they make the "extra" work attached to the less commonly used situation.

Note that when I say "less commonly used", that is not a "hard-and-fast-rule" for everyone, it is more of a general statement. Depending on the type of work that you do, you find find yourself using Absolute Range References more than Relative ones.