Conditional formatting Error - You cannot use a direct reference

[kpierce]

I started today with what I thought would be an easy task... Conditional formatting.

Column C in my work book with have a group name such as:
"Group 1"
"Group 2"
"Group 3"

Based on column C I want to turn the associated row,col A,B,C,D a certain color.

For example:
if C1="Group 1", I want A1:D1 to be filled with blue
if C2="Group 2", I want A2:D2 to be filled with purple.

this sounds easy enough and I can get it to work for one row. But when I paste the cells to get the formatting it does not work, when I try to select a range on the spreadsheet when creating the rule I get an error:
"You cannot use a direct reference to a worksheet...."

Any thoughts?

thanks so much.

 

[VoG]

Try selecting the whole range then enter the CF formula referring to the first row in the range.

 

[kpierce]

Thanks,
I think I tried that...

Here are my actual sets.
1)Highlight A1:D300
2)Select Conditional Formatting - Main Menu
3)Rule type = "select a formula to determine which cells to format
4)Select the button next to "format values where the formula is true"
5)Enter a range of: =$C$4:$C$69

Try to save rule and get the error.

I think you are saying for me to enter just C4 in step 5.
that works for row C4, but when I try to copy this down to row 5, etc... The conditional formatting says at C4.

What am I missing?

thanks as always.

 

[VoG]

1)Highlight A1:D300
2)Select Conditional Formatting - Main Menu
3)click New Rule
4)Click "use a formula..."
5)In the formula box =$C1="Group1"

 

[kpierce]

Awesome...

thanks again.