Hi, I'm working on a project at work and am pulling my hair out with some conditional formatting.
First of all I added Data Validation to two columns, the second column being conditional DV and this works a treat. The user pick from one of 4 options in first column and the options available int he next column are dependant upon their first selection. I was aware however that a user could work backwards i.e make a selection in the secong column first and this would then not match the correct option in the first column - SO I decided to add CONDITIONAL FORMATTING to the "Action" column, so that the text in this column turns Red if the wrong selection is made. This also works as I would like.
Now to the problem: in the third Column named "comments", this is where the user can just make any general notes or comments and I have done nothing to this column, however I have noticed that if a user attempts to write a comment in here and the cell is blank, their text turns Red and is effectively added to my range formula from "action" columns Conditional formatting. It's driving me crazy trying to rectify this. If there is already text in the comments column, then the user can add further text and it stays unformatted. The problem seems to be just with blank cells.

Does anybody have any ideas as to why this happens and how I can resolve it?
I have replacing any blank celles in "comments " column with a zero, however this is not an ideal solution. I have also switched off the "Extend data range formats and formulas" from the excel options menu, but yet again this is not an ideal solution because it only works on my computer and numerous users will be using the spreadsheet.

I have attached a small sample file of this problem, was wobdering if anyone could come up with an amendment to my formula that lies in the Data Validation in "action" column? At the end of formula where I have a "false" argument, this seems to keep changing all the time when you view it! also the range changes and adds in ceels from the comments column. I have just checked my sample sheet (attached) and this still happens, however guess what, the text entered in blank cells is not turning red now, however I know it will again because ive had the problem all week

Thanks in advance

Attached Files

Conditonal formatting and DV sample.xlsx (13.1 KB, 15 views)