Thread: Conditional formatting

Hi,

I have a problem, that if someone can solve it for me, will save me hours and hours of very boring copy and pasting!

Basically I have columns of values of different length (representing densities) and at the bottom of each column I have two numbers which represent the upper and lower tolerances for that density, i.e. the values the densities should be between.

I want to get excel to highlight in red all values that DO NOT fall within these tolerances. I can do this 100% fine for each individual column. However, I have hundreds of cloumns, each with its own unique upper and lower limits. therefore this would take hours everytime i alter the values to do all the columns individually.

eg.
545 456 345 << density values going down in columns, each column is a different set of values
547 425 565
589 562 675
612 345 345
722 745 654
569 456 456
754 545
546
456
550 560 400 << lower limit, at the end of each column is the lower limit followed by the higher limit
600 700 500 << Higher limit

i think what i need is to enter a formula in the conditional formatting that allows the upper and lower limits to change to those in the next column everytime, but i have no idea how to write such a formula!

Please help me!

thanks, graham

Are all those limits posted in the same rows? What rows are they?

Select all the cells prior to row 154 and go to Format|Conditional Formatting

Select Formula Is and enter formula =OR(A2<A$154,A2>A$155)

this assumes your A2 is the top-left most cell you select to highlight...adjust as necessary.

Click Format and choose Red from Pattern tab.

maybe you have numbers formatted as text?

Try this

Enter a 0 in any empty cell and copy it...

Then select the entire range of numbers, including rows 154 and 155... then go to Edit|Paste Special and click Add.

Then click Ok.

Does that fix the problem?

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Hi, I tried using the paste special and add, so I am now sure that all of my values are numbers and not text.

This still doesn't seem to solve the problem, the conditional formatting still colours everything, red. Even numbers that are inside the upper and lower tolerances, and I only want those numbers that are not within the upper and lower limits coloured.

here is a very small sample

475
Data^^______________________ 504____________row numbers
Average_______454.037 557.125 490.608 494.396 153
Upper tolerance 494.037 597.125 530.608 534.396 154
Lower tolerance 414.037 517.125 450.608 454.396 155

sorry underscores are to try and hold the columns

As you see the tolerances change for each column as they are calculated by +/- 40 from the average. Also not every column has a full set of readings.

Thanks very much for your replies so far, I am very appreciative of all your help. Sorry I didn't reply untill this morning.

Grahan

Please supply an actual spreadsheet sample that shows the problem.

You need to remove the $ from before the B in CF for B4...so that you don't make that column absolute... and then copy the cell select the whole range from B4:AA153 and go to Edit|Paste Special and select Formats... then Ok.....

... the only problem is that this will highlight all the blank rows as Excel sees these as below lower limit...

so you need to apply a formula instead... so try this...


Select B4:AA153 and apply

Formula Is:

=AND(B4<>"",OR(B4>B$154,B4<B$155))