Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to use conditional formatting on a cell that is populated via a validation list. It will not format. I try the exact same formatting on another cell which is not populated from a drop down list, and it formats the way I want it to. Does Excel not allow conditional formatting for items populated from drop down lists?

share|improve this question
    
I can't replicate this. I created a validation range, then had a bunch of cells validate using a list, pulled from the validation range. Conditional formatting worked fine. –  Paul Dec 15 '11 at 2:47
    
What is your conditional formatting equation and formatting and which cell is it applied to? –  Hand-E-Food Dec 15 '11 at 2:51
    
I have a validation list for column G. There are 3 choices for this list (open, canceled and reported). I want to format the cell in red if "canceled" is chosen. I use the conditional formatting as follows: –  sarah vac Dec 15 '11 at 3:31
    
cell value is equal to "canceled" –  sarah vac Dec 15 '11 at 3:39
    
I found something interesting. I can format for other options from this validation list. If I choose "open" it will format as I want it to. Excel does not seem to like the word "canceled" for some reason? I changed the option to read "canceled request" and the formatting is working fine. –  sarah vac Dec 15 '11 at 4:42

1 Answer 1

My guess it that you had a hidden space in your data validation so that your cell's value was "Canceled " instead of "Canceled". That would prevent your conditional formatting from matching.

For example, if you type in the list of words in the Source field, Data Validation ignores spaces after a comma but not spaces before a comma. The below screen print will cause the problem I described.

Data validation dialog box

share|improve this answer
    
this might actually be the cause –  datatoo Mar 3 '12 at 18:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.