Condtional Formating for range using IF formula based on cell

**clemsoncooz** · 02-06-2012, 11:08 AM

I need help with my conditional formatting.

I have data from A5 through I56. Columns D,F,and H are the only columns with which the user will enter data. The other columns are eithe labels or calculed cells.

I am trying to write a conditional formatting formula based below, but yet it doesnt seem to work correctly.

if looking at row 5, I want:
=If(D5<>"",1,0)

and if looking at row 6, I want:
=if(D6<>"",1,0)

so on and so worth though the entire range up till row 56.

And if true, then 'grey' out range A through I with grey fill.

My try was to try it for the range of A5 through I5 and write =If(D5<>"",1,0) with my format, and then just copy that rule for the entire range.....but the result is incorrect. When I write this rule and place a value in D5, it greys out A5 through F5, not through I5 as requested.

Sampel worksheet attached. Sheet1 is my tab with data. I also have another tab label INCORRECT OUTPUt and this just shows what happens with my current formula. And I also have a DESIRED OUTPUT to help show what I need.

Thanks for looking at this with me.

**zbor** · 02-06-2012, 11:10 AM

Change =If(D5<>"",1,0) to =If($D5<>"",1,0)

**clemsoncooz** · 02-06-2012, 11:16 AM

wow....I feel stupid.

Do you know why it was only "greying out" through Column F and not I, without the $ before D?

Thanks.

**zbor** · 02-07-2012, 05:29 AM

If you put without $ sign a formula in D5 "float" with cells as you move them.

So in E5 it become E5, inf F5 it become F5 etc...
Luckly, you had data in those cells so it grayed them out but once when you go out of range (or if you delete for example D5) it ignore CF.

Do keep them looking only in D5 you need to lock it $D5.