Excel 2007 : Extend conditional formatting

**Almson** · 05-12-2011, 03:09 AM

Hi!
I have a problem that I'm sure Excel can help me with but Ii don't know how!
I want to do conditional formatting on a large table with results from a metal analysis on soil. There are several different samples, and several different metals that I wnt to compare to the appropriate guide line values.
No problem doing this for one row, I simply choose all cells with analysis results, choose Conditional formatting -> highlight cells rules -> greater than, and choose the cell with the guideline value to get all cells with values higher than the guide line to go red.

But if I want to extend this formatting to the other 20 rows??
I could do the same thing all over again for every row, but that is very time consuming, is there any way to tell Excel to do the same kind of formatting (but using the guide line value of the new row) in every row?

I really hope you understand my problem and that some of you will find the time to help me with this!

Best regards,
Almson

**TMS** · 05-12-2011, 03:15 AM

I'm having some difficulty imagining what your data looks like and where the guide values are.

If you post a sample workbook, it should be very straightforward to work out a solution.

Regards

**Almson** · 05-12-2011, 04:44 AM

Good idea, I've attached a workbook to this message, where I've done cond. formatting on the "As" row. My guide line values are the two colored columns to the far right.
Thanks a lot!
/Almson

**TMS** · 05-12-2011, 05:03 AM

Change this: =$B$4:$CU$4 to this: =$B4:$CU4 and change: $CW$4 to $CW4 for the first condition and $CV$4 to $CV4 for the second condition.

You can then extend the formatting to all the rows.

Regards

**Almson** · 05-12-2011, 05:16 AM

Super! Thanks a lot for your help!
That will save me so much time, I work a lot with these kind of problems!

Perhaps you can help me with another formatting problem?
As you can see in my worksheet I have some values that are e.g. "<0.1" which means they are lower than the detection limit at the laboratory. When I do cond. formatting they go red, i.e. they look like they are larger than the guide line value when they in fact are a lot lower. Can I do something to get rid of this too? (What I do now is that I clear the formatting rule from those cells, but that's also time consuming...)

Thanks again!