Custom formatting merged cells

[jblevins]

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?

 

[Joe4]

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.

 

[jblevins]

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.

 

[Joe4]

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?

 

[jblevins]

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.

=INDIRECT(TEXT(MONTH(J3)*29,"mmm")&"!F24")<>""

 

[Joe4]

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

 

[jblevins]

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.

 

[Joe4]

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)?

 

[jblevins]

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...

        =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.

 

[Joe4]

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)?