Merged Cells and Conditional Formatting

[dan_movie]

I have a condition set that changes the fill color to red when my condition is true.
This is on a time field. My bug is that it's like if the condition evaluates each cell in the merged group - the result is the 1st cell is too small so it displays ## the 2nd cell displays the correct time.
If I change the cell to which the conditional formatting is applied and reference ONLY the 2nd cell in the merged cells that my condition is never true because excel seems to consider the merged cell to be named by the 1st cell.

Let me try to clarify... I have G84 & G85 merged (with G84 being the narrower cell). When I click on the merged cell the name box near the top left of Excel shows that my selected cell is G84.
When I apply my conditional rules to the merged cell it is automatically applied to the cell group so $G$84:$G$85.
This causes the smaller cell to display hashtags (##). If I remove the smaller cell for the conditional formatting and make it $G$85 only, then my condition never triggers because for excel (based on the name box) I never enter cell G85 but rather a merged G84 cell.

Any ideas on how to fix this ? Is this by design or a bug with merged cells ? I am using Excel 2016.

Thanks,
Dan

 

[Fluff]

Firstly: Merged cells are an abomination & should be avoided like the plague.
Secondly: See above.

I have G84 & G85 merged (with G84 being the narrower cell).

Unfortunately this makes no sense as all cells in a particular column will be the same width.
Moreover if they merged then it's only one cell (ish) with one six

it's like if the condition evaluates each cell in the merged group

That's exactly what it does, along with normal formulae. Please see point 2 above.

If I change the cell to which the conditional formatting is applied and reference ONLY the 2nd cell in the merged cells that my condition is never true because excel seems to consider the merged cell to be named by the 1st cell.

The formula is probably never true as G85 has no value as it's empty G84 is the only one of the two cells that will have value.

Any ideas on how to fix this ?

Yes get rid of all merged cells.

They could well cause you all sorts of problems in the future.

 

[dan_movie]

AH CRAP !! I wrote my message too fast !!

The cells I am referring to are G84 & H84. When merged the name box only sees G84.
That is the narrow column that results in hashtags (##) with H84 showing the correct time and both cells showing the conditional fill.

What I don't understand in the behavior is... if the cells are merged and the data is entered in the G84 cell why is there data in the H84 cell if it "does not exist" because it is merged.

 

[Fluff]

Can you post an image of what you mean?
Also how is the information getting into those cells?

 

[dan_movie]

Merged_cells-Conditional_formatting.xlsx
	A	B	C	D	E	F
1			MINUTES		SET-UP TIME
2	VFX PREVIOUS		0:15:10		0:30:00
3	VFX TODAY		1:31:43
4	VFX TOTAL TO DATE		1:46:53		0:30:00
5	Footage Shot Today		0:22:00		8:42:00
PR Front

Cell Formulas
Range	Formula
C4,E4	C4	=(C2+C3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:F3	Expression	=$C$3+$E$3>$E$5	text	NO
E3:F3	Cell	contains a blank value	text	NO

 

[Joe4]

The cells I am referring to are G84 & H84. When merged the name box only sees G84.

Good news! If you are merging cells across rows like that, you can easily replace them with cells that use the "Center Across Selection" formatting option.
This gives you exactly the same visual effect as merged cells, but without all the issues.
See here how to do that: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

So I would highly recommend replacing all the merged cells with this.

 

[dan_movie]

3 images.... with blank cell (yellow fill), with value below trigger threshold, with cell having triggered the conditional formatting (red fill)
Data is entered manually into the merged E3 cell.

Thanks for the help.
Dan

 

[Fluff]

As the Cf rule is just changing the fill colour, it has nothing to do with the fact that you seem to have two values in the cell.
Best option is to get rid of the merged cells, you will be glad you did.
See Joe's comments & link in post#6

 

[dan_movie]

Hi All,

I un-merged the cells, ran my CF formatting on the left cell (E3). I noticed in my Excel version I can't change the alignment within the CF options.
When I select E3 & F3 cells to use the "center across selection" alignment it works on data that does not trigger the CF.
As soon as the CF condition is fired up, the cell content is only on the E3 cell and not centered across selection (which is what is set in the cells) - the CF formatting seems to override the cell alignment properties.

All this to say... back to square one, same problem as before. Below : Red fill number when CF trigger fires, White fill is the default - no CF rule, Yellow is when E3 is empty CF fires and turns both cells E3 & F3 to yellow.

 

[Fluff]

Not sure why that is happening to you, as I cannot replicate that behaviour.
If there any reason why you cannot just use col C for Minutes & col D for Set-up & make them wide enough for the data?