Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
Any feedback always appreciated. I beg you do not just cast a mute down vote. –  matcheek Mar 20 '12 at 10:24

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
That worked fine. Cheers –  matcheek Mar 20 '12 at 13:39

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

share|improve this answer
    
Cheers for that. However it seems to me that it highlights blank cells too. Any thoughts on that –  matcheek Mar 20 '12 at 11:23
    
Make sure your blank cells are actually blank. Try moving your data to a new worksheet and see if it still happens, it shouldn't –  Raystafarian Mar 20 '12 at 11:25
    
No luck, it does it on all worksheets :( –  matcheek Mar 20 '12 at 11:34
    
Open a new workbook, create some new data and apply the formatting. See if it behaves the same when you do that. It shouldn't. –  Raystafarian Mar 20 '12 at 12:14
    
Exactly the same thing. I have created a new file, not only a new workbook this time, all blank cells highlighted. –  matcheek Mar 20 '12 at 13:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.