I have a Spread Sheet I am using as an Excel progam...

I was wondering how I can make an IF Then function that will fill in an entire designated amount of rows with background color.  I have been experimenting with the Conditional Formatting Rules, however, the fill pattern I wish to use is only applicable to 1 column, which checks whether the Level amount is greater than the one input by the user.  I want it to fill in the entire row with the fill pattern, when the statement is true to the IF statement, and do nothing to the background or the rows that it isn't True for.  The problem is that I also have some zeros as values, and words that are names of objects that make this difficult for me to figure out myself.  Please help me if you can.  Thank you.

Manuel Macedo

mjmacedo03@gmail.com

You can use conditional formatting to achieve the effect that you describe - see for example

http://www.contextures.com/xlCondFormat02.html

Remember that conditonal formatting is another formula that Excel has to keep track of and could potentially make your workbook run slowly. Why not use a filter to hide the rows of data that do not fit your criteria?

Peter

Thank you!!!  This was extremely helpful.  I was curious though, on how I would use a filter to hide rows under a designated value.  One last thing though, how do I prompt a message say, if my value I do not want to exceed 250?  This way the user can not enter a value over it?  The problem there is it is linked to another spreadsheet and becomes a circular reference?  I am just trying to make it say, "N/A", then return it to the last correct value entered.  If you know how to do this, please help me.  If you do not, thank you greatly for all your help thus far.

Manuel Macedo

In answer to your first question:
In Excel2003, use the custom autofilter "is greater than" on the autofilter arrow
In Excel2010, use the Number Filters item on the autofilter arrow.

In answer to your second questions:
You could put a data validation condition on the cell in question using the Custom data validation with a formula such as =CellRef<250. You can customize the information message for the user and the cell value will return to the previous cell value if the user doesn't enter a valid number.

Peter

Thank you again!!!!  the Data validation worked great!!!  Is there a way to limit the options on the bottom of the message function?  A way to get rid of the "Retry" and "Help" options in it perhaps?  

The Data Validation dialogue box gives some limited ability to customize what is shown to the user but I don't think that you can change it in the way in which you describe. Excel2010 offers a "Circle Invalid Data" option that I don't think is available in Excel2003.

If you want anything else then you will have to write your own data validation code in VBA and use the Worksheet_Change event to run it.

Peter