How to use Excel Data Validation to ensure a cell value is > than another Cell and to Color format Cells that with > values?

Question

Greetings,

I have a very large Excel Sheet to gather Data and use to calculate other fields based on some of the sheet fields. I need to make sure that with some data validations guide users to enter logical data. For example there are 2 fields [OutsideDiameter] and [InsideDiameter]. I need to ensure that values entered for [InsideDiameter] field is not >= [OutsideDiameter].

Second, I need to use color formatting to alert and expose these cells that doesn't meet this condition by using the Conditional Format rules.

Thanks for any help.

P/N: I use the data validation as shown, but the problem with that is if OUTSIDEDIAMETER has no value, the Validation doesn't work!!

Below is a sample data

Answer 1

Select AB3:AB10,

Data Validation, AB3 the first cell of your selected range.

=AB3>AA3

When I enter 5 in AB4, it will get error.

For conditional formatting.

Select AB3:AB10, conditional formatting>Use a formula...

=AB3<AA3

Answer 2

Greetings Snow Lu.

Thank you for replay. This is working similar to the option I used, which has the same shortcoming. So, if the "compared to" cell has blank value, the validation will not work. Example, if AA6 is Blank and I entered 3 in cell AB6, the validation will not work. After that, I then can enter values in Cell AA6 that are greater than AB6, which are violating the validation rule.

I also thank you for the format tip.

Answer 3

You may set another separate rule in AA column, then it will check both AA column and AB column.