Excel Conditional Formatting

Question

Highlight all non-blank entries in a column, except for the column header, that do no fall in some range. Here's my attempt:

=AND(OR(VALUE(INDIRECT("H"&ROW()))<0,VALUE(INDIRECT("H"&ROW()))>100),ROW()>1)

Evaluates as expected if pasted in a cell. Used in conditional formatting does not. Anybody knows why?

Answer 1

Select whole column H and use this formula in conditional formatting

=OR(H1<0,H1>100)

That will adjust automatically for each row (you don't need INDIRECT here)

Unless H1 is a number that satisfies the conditions you don't need to exclude H1, if it is then change the "applies to" range as Raystafarian suggests

Answer 2

it looks like you're trying to get values less than zero or greater than 1, so you can look at it as not between

the easy way to do this is without a formula: conditional formatting - highlight cell rules - more rules go with cell value and not between 0 and 1 and hit okay

if you want to exclude the header go to conditional formatting - manage rules and change the applies to range.

link to some good info