• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting on the border of a Merged Cell

F

fred

Member
Hi, I'm using MS2007.


In cell A1: any number from 1 to 60 (number of months, manual input)


One of the conditions I need to show is that if the A1 is 1 to 12. Then in cells A3:A14 (merged into one cell) a text message would show "Please input data on the column to the right." so the person using the spreadsheet would have to provide more input in cells B3:B14. if A1 equals to 1, 2, 3,...or 12.


My problem is that when I set up the condition. I cannot make the border of A3:A14 to show up properly. The condition "Applies to" clearly says "=$A$3:$A$14" where the border should be a red line surrounding the Merged cell in A3:A14. But I only see A3 partially covered with a red line (left, right and top). The rest of the merged cell from A4 to A14 does not show the red line per conditional formatting.


Did I do something wrong on the conditional formatting?
 
In A3 you can put an formula: =If(A1<=12,"Please input data on the column to the right.","")


Putting borders around merged cells works ok for me using Conditional formatting

Your range worries me

When you use merged cells you only refer to them by the Upper Left corner cells reference, in your case A3


When you move into the range A3:A14, the whole area A3:A14 should be selected without any internal borders

Are you sure they are merged ?
 
yeah, the whole section is merged and centered. I don't want to display 12 cells of "please input data on the column to the right". I know that will work on conditional formating. I just find it difficult to set the condition to a block of merged cells.


the cell color conditional formating was done right. it's just the border that excel recognize the first cell (red line on right, left and top) while there is no red line border from cells 2 to 12.
 
Fred

Do you want to email me the file

email is at bottom of http://chandoo.org/wp/about-hui/
 
Fred

Select The merged cells B3

Conditional Format

Manage Rules

Edit Rule

Use a Formula to determine which cells to format

Enter the following formula =B$1<=12

Ok

Apply

Ok


Enjoy...
 
Thanks, Hui! it works like magic. Thank you very much indeed!


One question though:


What is the significance of adding "$" after "B"?

- I tried "B1" without a "$" and one of the merged cells has no borders on either side

- I tried "$B1" and the same, only one, merged cell has no borders on either side

- I tried "$B$1" and it works just the same as "B$1"


Why "$B$1" or "B$1" work but not when it is "B1" or "$B1"? That puzzles me.
 
The $ isn't after the B it is before the 1, a subtle but important Difference

The $ sign tells Excel that the location of 1 is fixed for all the cells in the range to which the CF is applied.

Without the $ sign excel would use B1 for B3, B2 for B4, B3 for B5 etc

With the $ sign excel uses B1 for B3, B4, B5 etc
 
You must log in or register to reply here.
Back
Top