Conditional format not applied to whole range of cells

[teachman]

Hello,

I'm having issues with conditional formatting being applied across a contiguous range of cells.

Set up: BA133 is a merged set of BA133, BB133, BC133
BD133 is a merged set of BD133, BE133, BF133
BG133 is a merged set of BG133, BH133, BI133

What I want to do is if the total of BA133 + BD133 + BG133 > 100 then the whole range of BA133 thru BI133 be formatted to a red fill.

What happens is only the merged BA133 is set to red.

The value in the formula field is: =(BA133 + BD133 + BG133 > 100
The value in the Applied To field is: $BG$133:$BI133

Just when I think I understand conditional formatting, something like this pops up.

Any help will be much appreciated.

George Teachman

 

[Jonmo1]

You have to lock the column in the references.

=($BA133 + $BD133 + $BG133) > 100

You could also do this (because only the top/left cell of a merged range actually contains a value)
=SUM($BA133:$BG133) > 100