Copy/Paste/Format Paint For Conditional Formatting

Hi,

I have a spreadsheet set up. It's a football result predictor, shown below:

Lucky 7's Picture.jpg

So basically this is done on a weekly basis. So D2 - L8 look to C2 - C8 in order to see if the result is correct. If it is a match the rule says if equal to then green but if not equal to then red. I am now setting this up for the whole season so copying and pasting a blank table for say 40 weeks to cover a season. I did this exactly the same last season. The problem I had which i'm pretty sure will be an easy solution for a guru but not for me is that once I copy and paste the table underneath the conditional formatting for the H, A or D (Home, Away or Draw) column relies on its original cell. Not sure I have explained this great but the picture below hopefully makes it obvious.

Problem.jpg

So as can be seen in the photo I have set C4 to A (Away) but this has also applied to D14 - L14. This is obviously because D14 - L14 conditional formatting is exactly the same as D2 - L8 so is still looking at C2 - C8 for its formatting. I know I can go into the formula of each cell after copying and pasting and change to look at C12 - C18 instead but this is going to be a pain to do this for 9x6 Cells individually for potentially 40 game weeks. Sorry if I have explained this poorly but I am hoping the pictures clear it up a bit. Any help would be greatly appreciated.

Kristopher

What kind of CF rule are you using?
Not sure I understand completely, but...
I would think you need to set up a proper CF rule in the 1st cell (D2) like $C2="A", then use Format Painter not Copy/Paste on all the other cells and the rule will apply as $C3="A", $C4="A" and so on.

The rules for cell D2 can be seen below:

Rules.jpg

So these same 2 rules are applied from D2 - L2. Which means all these cells are either equal/not equal to C2 and this is how they are either green/red. So Cell D3 should be equal/not equal to C3. The problem is that it will not look at C3 through copy and paste or format painter it always sticks to C2. I have copied and pasted the table below as I intend on doing and have opened the rules for D12. This shows the problem as I need D12 to look at C12 but automatically still looks to C2.

Rules Copy and Paste.jpg

Did you try my suggestion? Remove the "$" before the row index and use Format Painter to apply the rule.