What's wrong with my conditional formatting?

Topic

New Reply

This topic is about the spreadsheet that I have attached to [post=”792073″]Post 792073[/post]

I have created a new thread as the original poster has not yet responded and I don’t want to cause any confusion on their first post in the lounge.

The spreadsheet uses identical conditional formatting in cells F6 to H16, but it behaves differently in some of the cells.

If I enter random text (such as “aa”) into the cells then some cells display the validation stop message (G7:G12, G14:G16, H6, H13). All the other cells allow me to enter this text, even though it is not in the validation list.

I cannot see any difference in the cells that would account for this difference in behaviour.

Reply | Quote

Replies

Appears to be the “Data Validation Formula” was copied across and absolute wasn’t applied.

So Col E & F work fine, but Column G now refers to Column F and Column H refers to Column G.

That’s if I understand correctly.

Reply | Quote

Appears to be the “Data Validation Formula” was copied across and absolute wasn’t applied.

So Col E & F work fine, but Column G now refers to Column F and Column H refers to Column G.

That’s if I understand correctly.

This is not the problem, column G is meant to refer to column F, and H to G. That was intentional.
What seems to be happening is that the validation drop down list always looks right, but Excel allows me to type invald content into the cells IF the cell to the left is empty, but if I have “aa” or something else invalid in the cell to the left then I get the Validation Stop error.

Reply | Quote

Hi Stuart,

I can see your problem as stated, but don’t understand why the error only populates in the left hand blank cell. If you enter an invalid item into the cells from right to left (for example), then the “stop” error fails until the left hand blank cell, at which point the stop works.

I can’t see any logic as to why.

I’m wondering if an active cell checker in the change event might be more robust, leaving the data validation in place, but without the stop error being active. ??

Reply | Quote

You need to uncheck the Ignore Blank option. I’m not sure exactly what the underlying cause is (it can also cause issues with named range validation), but my general rule is to always uncheck that option.

Reply | Quote

You need to uncheck the Ignore Blank option. I’m not sure exactly what the underlying cause is (it can also cause issues with named range validation), but my general rule is to always uncheck that option.

Thank you, I would never have guessed that one.

Reply | Quote