Custom formatting merged cells

jblevins

Active Member
Joined
Sep 2, 2013
Messages
250
Office Version
  1. 2003 or older
When using conditional formatting for the border of merged cells, only the left most cell is formatted, is there a way to conditional format the entire merge?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Seems to work just fine for me.
But you must be sure to select ALL the cells in the merged range to apply it to.
And be sure to choose the "Outline" border option.

1688051519280.png
 
Upvote 0
Seems to work just fine for me.
But you must be sure to select ALL the cells in the merged range to apply it to.
And be sure to choose the "Outline" border option.

View attachment 94440
I assume you are using "Format" > "Conditional Formatting" and not just format. If so, it is probably because my Office 2003 does not have the same ability as your version.
 
Upvote 0
Yep, I used Conditional Formatting.
I could be wrong, but I don't think there should be anything special that changed from 2003 to now that would affect this.

Can you show us an example?
What range is merged?
Exactly how are you applying the Conditional Formatting?
 
Upvote 0
This is a perpetual calendar which changes based on what is entered on the individual calendars, look at the bottom right where it says "Notes", only the far left cell has a border on top and I have tried the border Outline option.
Excel Formula:
=INDIRECT(TEXT(MONTH(J3)*29,"mmm")&"!F24")<>""
 

Attachments

  • Cal.jpg
    Cal.jpg
    26.1 KB · Views: 9
Upvote 0
Upvote 0
Merged cells are notoriously problematic (and quite frankly, an abomination that most programmers won't touch).
Since you seem to be merging multiple cells across a single row, I wonder if you might have more success using the "Center Across Selection" formatting option, which gives you the same visual effect as merged cells without all the issues.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis
To do that, I would have to have an individual conditional format for each cell. Guess I will take a different route and forget my original intentions... My thanks to everyone and God bless.
 
Upvote 0
To do that, I would have to have an individual conditional format for each cell
I believe that what may be happening now, which is why you are only seeing that one cell being formatted.

There may be other ways to do it (or tweaks to the current process), but we would need a bit more detail. Your formula is referencing a few different cells, so we would need to know:
1. What range exactly your calendar is in now
2. What are in the cells your formula is referencing
3. Exactly how you have set-up the existing Conditonal Formatting

Are these calendar being set up manually or dynamically?
1. How are the merged cells originally being set?
2. How is the green coloring being set (manually or by Conditional Formatting)?
 
Upvote 0
I believe that what may be happening now, which is why you are only seeing that one cell being formatted.

There may be other ways to do it (or tweaks to the current process), but we would need a bit more detail. Your formula is referencing a few different cells, so we would need to know:
1. What range exactly your calendar is in now
2. What are in the cells your formula is referencing
3. Exactly how you have set-up the existing Conditonal Formatting

Are these calendar being set up manually or dynamically?
1. How are the merged cells originally being set?
2. How is the green coloring being set (manually or by Conditional Formatting)?
There are 13 sheets to the calendar - Year; Jan; Feb; Mar; etc...
Excel Formula:
        =INDIRECT(TEXT(MONTH(J3)*29,"mmm")&"!F24")<>""
The "J3" in the formula is the name of the month and the "F24" is the location on the sheet that is pointed to by "J3".
The merged cells are manually fixed.
The green and the border comes from Conditional Formatting.

When a change is made on one of the month sheets, the "Year" sheet sees it as stated above and the color and border of the relative cell is changed by Conditional Formating.
 
Upvote 0
Are the Conditional Formatting rules that turn the cells green and apply the border the same CF rule, or different CF rules?

If they are different rules, do they follow the same exact formula on the exact same range?

And did you specifically choose the "Outline" border option (instead of picking each border line separately)?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top