PDA

View Full Version : Solved: go through column and highlight cells



grichey
03-11-2008, 09:31 AM
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
03-11-2008, 09:36 AM
Hi there,

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

grichey
03-11-2008, 09:49 AM
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
03-11-2008, 09:51 AM
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
03-11-2008, 11:40 AM
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
03-11-2008, 12:43 PM
I should say to pick up values in the case of the sample file > abs(.1)

Bob Phillips
03-11-2008, 12:45 PM
A CF formula of

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

works for me

Zack Barresse
03-11-2008, 12:51 PM
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
03-11-2008, 01:43 PM
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
03-12-2008, 10:53 AM
Ok, then adjust the numbers accordingly...


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

grichey
03-12-2008, 11:14 AM
roger -- thanks for the help