Conditional Formatting disappearing after unmerging cells

WedgeExcel

New Member
Joined
Jan 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi there! I'm working on a schedule, date being on the X-axis and the relevant parties on the Y-axis. Each row uses conditional formatting to colour it when there is an input in the cell. Often, an activity drags out over several days and to fit the name of the activity and for the sake of aesthetics, we merge cells. I've made a macro that reformats the scedule's borders, but I'm having a hard time figuring out how to automatically re-apply the conditional formating the sheet uses to the cells appear after unmerging a cell.

Tldr: how can I get Excel to automatically reapply conditional formating to the cells that appear after unmerging cells?

Thanks in advance!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,202
Office Version
  1. 2010
Best way ? DO NOT use merged cells, they are one of the worst features Excel has
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,744
Office Version
  1. 365
Platform
  1. Windows
Solution

WedgeExcel

New Member
Joined
Jan 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the Board!

Note that if you are just merging multiple columns along single rows, you can achieve the exact same "aesthetic" appearance as merged cells without all the issues it causes by using the "Center Across Selection" formatting option instead.

See here for more details: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis
Thanks for the input! I've been toying around with that option for a bit now, but it doesn't seem as if conditional formatting wants to work with that input. Would a script to "refresh" the area the conditional formatting applies to be possible instead?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,744
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I think we need to see the data your are talking about. Can you show us your data and expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

WedgeExcel

New Member
Joined
Jan 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I think we need to see the data your are talking about. Can you show us your data and expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Thanks for the reply! I've decided to go with a different method. Instead of merging, as has been stated is a bad sollution, I'll use macros to format cells to center across selected and colour them appropriately.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,744
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad that you have a solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,126
Messages
5,628,858
Members
416,345
Latest member
sayad

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