You could add a column that tracks your search results, using it as a
binary. Then, you can set the row highlighting based on the value of
that cell. The query sets the value of that cell in each row when run,
and the query should reset the values just before performing the query.

I have a workbook which I use to print data from, and the rows I print
are based on a Y/N value of a single column. I also have rows which get
set yellow bckgnd (psuedo-highlighted) based on a Yes/No column flag
status.

I use the conditional formatting to format the row IF the 'flag' value
in the search query result column is "Y"

Then, all you would need to do is add the column, and write the query
to alter the value of the cells in that column, and those rows will
automatically highlight.

The bonus here is that if you manually set the flag to "Y", the row will
highlight without the need to run the query.

Another plus would be to use the column to store results of other
queries as well, so another type query would enter say a "K" in the cell,
and all rows with that flag set to K would highlight in green instead of
yellow. Some types of uses like that would be what you could do by using
a query results column.

I'll post a link to a sample soon.


On Mon, 9 Jan 2012 17:25:59 -0800 (PST), John Menken
wrote:

Hi. I am using Excel 2010 and here is what I am trying to do. I have a
spreadsheet with many columns, three of which are shown below. I need
to have a message box pop up that says "What is the report date" which
then gives the user the ability to type in a date. Once the date has
been entered the code looks through the data and in any situation
where it finds YOS is less than one **and** SrvcPlus1YrMINUS90days is
less than the message box date, the entire row is highlighted in
yellow. Can anyone give me some help on what this code might look
like? Thanks very much.

YOS ServiceDtPlus1Year SrvcPlus1YrMINUS90days
2.26 10/01/2010 07/03/2010
1.22 10/18/2011 07/20/2011
1.11 11/29/2011 08/31/2011
.75 04/06/2012 01/07/2012
.13 11/21/2012 08/23/2012
2.89 02/16/2010 11/18/2009
1.97 01/18/2011 10/20/2010
1.49 07/12/2011 04/13/2011
.76 04/01/2012 01/02/2012
.53 06/27/2012 03/29/2012
.49 07/11/2012 04/12/2012
.20 10/26/2012 07/28/2012
.12 11/23/2012 08/25/2012
.12 11/23/2012 08/25/2012
.09 12/05/2012 09/06/2012
.04 12/21/2012 09/22/2012
4.40 08/13/2008 05/15/2008
4.01 01/02/2009 10/04/2008
3.28 09/25/2009 06/27/2009
3.14 11/15/2009 08/17/2009
1.72 04/19/2011 01/19/2011
1.67 05/04/2011 02/03/2011
1.67 05/06/2011 02/05/2011
1.61 05/26/2011 02/25/2011
1.61 05/29/2011 02/28/2011
1.57 06/11/2011 03/13/2011
1.57 06/11/2011 03/13/2011
1.26 10/01/2011 07/03/2011
1.20 10/25/2011 07/27/2011
1.01 01/03/2012 10/05/2011
1.00 01/06/2012 10/08/2011
.98 01/14/2012 10/16/2011
.92 02/07/2012 11/09/2011
.92 02/07/2012 11/09/2011
.79 03/21/2012 12/22/2011
.79 03/21/2012 12/22/2011
.75 04/05/2012 01/06/2012
.72 04/18/2012 01/19/2012
.51 07/01/2012 04/02/2012
.46 07/21/2012 04/22/2012
.35 09/01/2012 06/03/2012
.35 09/01/2012 06/03/2012
.35 09/01/2012 06/03/2012
.12 11/25/2012 08/27/2012
5.26 10/03/2007 07/05/2007
4.47 07/16/2008 04/17/2008
3.81 03/14/2009 12/14/2008
3.51 07/01/2009 04/02/2009
3.44 07/28/2009 04/29/2009
3.07 12/10/2009 09/11/2009
2.24 10/09/2010 07/11/2010
2.23 10/14/2010 07/16/2010
2.04 12/22/2010 09/23/2010
2.04 12/22/2010 09/23/2010
1.85 03/01/2011 12/01/2010
1.78 03/26/2011 12/26/2010
1.67 05/04/2011 02/03/2011
1.67 05/05/2011 02/04/2011
1.67 05/05/2011 02/04/2011
1.61 05/28/2011 02/27/2011
1.61 05/29/2011 02/28/2011
1.53 06/28/2011 03/30/2011
1.42 08/06/2011 05/08/2011
1.42 08/06/2011 05/08/2011
1.29 09/23/2011 06/25/2011
1.12 11/23/2011 08/25/2011
1.07 12/10/2011 09/11/2011
.97 01/19/2012 10/21/2011
.90 02/14/2012 11/16/2011
.88 02/22/2012 11/24/2011
.87 02/25/2012 11/27/2011