Conditional Formatting Conundrum

**kwelch66** · 01-18-2014, 12:38 PM

Hello All:

First time poster here.

I was hoping someone could give me some advice on how to solve this issue. I should first let you know this is just for some fantasy baseball stuff, so nothing of major importance.

I have conditional formatting set-up that if a (C, 1B, 2B or SS) shows up in B4, then B4:C7 will highlight as a certain color. I set-up four different conditions to do this.

The problem is, now I want to do them same for the rest of the spreadsheet. I can't seem to just copy and paste that formula to other areas. It always refers back to what is in cell B4. If I try removing the $ sign, the entire B4:C7 will not highlight, just a portion will. Do I have to do this manually to each area.

What am I missing here? Thanks for your help.

Kyle

Screen Shot 2014-01-18 at 11.22.51 AM.png

**Pepe Le Mokko** · 01-18-2014, 01:03 PM

Hi
merged cells are very pretty but a pain to work with . That is why they should be avoided at all costs except for cosmetic purposes.
It would be easier to redesign your sheet without merged cells first.
If you're still stuck, pleas post a sample sheet ( no pic) - Thx

**martindwilson** · 01-18-2014, 02:17 PM

if you insist on merged cells then you have to revise the formula to reference the same cell in each block
so for say b4:s100
you'd apply for say ="C"
cf format use a formula option to b4 of
=INDEX($B$4:$S$100,CEILING(ROWS($A$1:$A1)/4,1)*4-3,CEILING(COLUMNS($A$1:A$1)/2,1)*2-1)="c"