Merged Cells and Conditional Formatting

dan_movie

New Member
Joined
Dec 13, 2016
Messages
10
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
 

dan_movie

New Member
Joined
Dec 13, 2016
Messages
10
Thanks for the help.

Other areas of the sheet have a different layout that require smaller cells, etc.
I will live ! It just bugged me that I could not get the merged cells to work as I thought they should and wanted to double check my CF logic in case I had the rules all wrong.
Not really worth everyone's debugging time ! Thanks again for the help.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
Sometimes, when working with Conditional Formatting and you have multiple rules on multiple ranges, it is best to just remove all the rules and start over. I have seen rules get pretty messed up when ranges and rules are adjusted after the initial rules get created.

But whatever you end up doing, I highly, HIGHLY recommend getting rid of those merged cells - permanently!
(especially when there is such a viable alternative that does not cause all the same problems)
If not, they are likely to cause you more grief somewhere down the road.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,015
Messages
5,628,146
Members
416,294
Latest member
McStuffins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top