2

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?

5
  • 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, 2011 at 2:47
  • What is your conditional formatting equation and formatting and which cell is it applied to? Dec 15, 2011 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, 2011 at 3:31
  • cell value is equal to "canceled"
    – sarah vac
    Dec 15, 2011 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, 2011 at 4:42

1 Answer 1

0

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

1
  • this might actually be the cause
    – datatoo
    Mar 3, 2012 at 18:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .