Solved: go through column and highlight cells

[grichey]

The goal is to go through a column and if the number is say over or under .1 to high light in red (or any color for that matter).

How do you step through each cell going down a column?

[Zack Barresse]

Hi there,

Not sure what you mean. Conditional Formatting will take care of it though. If you need more information, so do we.

[grichey]

I have a spreadsheet with a column x with data in some rows from 1 to 2000. What I'm trying to do is go through those 2000 rows and if there is data in them and the value is greater than .1, put fill.color to red. What else would be helpful?

[Zack Barresse]

Do you not want to use Conditional Formatting?? This is what you need. If you need more, describe your data in great detail, or post a sample file.

[grichey]

Here's a sample file. I've tried using conditional formatting but I can't get it to treat blanks, div/0, etc correctly. I'm trying to only pick up the values for color when there's an actual value there in the % column. My original thought was to just step through the column cell by cell and evaluate each one then change fill color as the criteria dictate.

thanks

[grichey]

I should say to pick up values in the case of the sample file > abs(.1)

[Bob Phillips]

A CF formula of

=AND(B3<>"",B3<0.1)

works for me

[Zack Barresse]

But in your example, all values fall into the less than -1% or greater than 1% range. Select the entire column H, add conditional formatting formula of ...

=($A1<>"")*(ISNUMBER($H1))*(($H1<-0.01)+($H1>0.01))

Edit: Is it 0.1%, or 0.1?

[grichey]

But in your example, all values fall into the less than -1% or greater than 1% range. Select the entire column H, add conditional formatting formula of ...

=($A1<>"")*(ISNUMBER($H1))*(($H1<-0.01)+($H1>0.01))

Edit: Is it 0.1%, or 0.1?

.1 or 10 %

[Zack Barresse]

Ok, then adjust the numbers accordingly...

=($A1<>"")*(ISNUMBER($H1))*(($H1<-0.1)+($H1>0.1))

[grichey]

roger -- thanks for the help