Excel 2007: Conditional format with text input

Yes, you can do it. See attached file.

Sincerely,
Ed
Formatting.xlsx

Do you have anything currently in cell C1 ?

If not, you can put a formula like =B1>100, use a custom format like "Please Explain";;
and add the conditional format on top of that for the color

Thomas

I apologize for not making myself clear.  I would like to do it without putting a formula in the cell.  In other words, if B1 is >100, the words "Please Explain" will be in the cell, otherwise the cell would be blank?  Is it doable without VB?

Not possible without VB as far as I know

T

Not with the cell blank, no. If you have something in there, even if just a space, then yes, but otherwise you would need VB as Thomas said.
I guess my first question would be why does C1 need to be blank if you want it to appear to have text in?

I guess it doesn't have to be blank but I felt it would look cleaner. The text "Please Explain" seems intuitive for user to overwrite with comment.  I prefer to see only cells that required comments with text and those that doesn't blank or space when I move down a column.

But the text would only appear for those cells where applicable. The rest of the cells would appear empty - that was the point of Thomas' suggestion.

Sorry, I missed out a line of explanation.

When comment is added in column D, the "Please explain" disappears because column C cannot overflow into the column D cell. When comment is deleted the "Please explain" re-appears.

Thanks
Rob H

Rob - Not a bad alternative but a couple of potential issues:

If the cells in column C are set to Locked and the sheet is protected with Select Locked Cells unticked as you were suggesting, cells in column C can't be selected and moving right from column B goes straight to column D and vice versa.

Thanks
Rob H

In addition, row and column headers and gridlines could be hidden so the viewer is none the wiser.

Thanks
Rob H

True enough, but seems like more setup/sacrifice for essentially the same result I proposed.

Another alternative - set up Data Validation on Column B as follows:

- Select Column B
- Select Data tab > Data Validation > Settings tab
   - Allow: Whole Number (or Decimal if appropriate)
   - Data: less than or equal to
   - Maximum: 100
- Error Alert tab
   - Style: Information (or Warning)
   - Title: Explanation Needed (or any other preferred text)
   - Error Message: "Column B values over 100 require an explanation in Column C" (or any other preferred text)

- Select Column C
- Select Conditional Formatting > Highlight Cell Rules > More Rules > Use a formula to determine which cells to format
- Format values where this formula is true: =B1>100

This removes the issue of manipulating the cell widths/protection/etc. while achieving (almost) the same result.  The biggest difference is that the "Please explain" prompt is at time of entry and isn't persistent once they leave the current cell (although the cell highlighting remains).  Another advantage, however, is that it requires a deliberate user action to acknowledge the explanation request.

 Formatting.xlsx

Mark and Bob, I appreciate your suggestions and the creativity.  I have read each one carefully and have determined to use a combination of techniques both of you suggested that best fit my situation.  I will use the additional narrow column and lock and hide the column upon protection.  I will allow selection locked cells since there are other cells in the worksheet I want to let user select.

Thanks you very much!