Find then highlight in yellow

  • Thread starter Thread starter John Menken
  • Start date Start date
J

John Menken

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
 
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.
 
John said:
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
Click to expand...

This assumes that YOS is column A, SrvcPlus1YrMINUS90days is column C, and
the posted data is all that's on the spreadsheet.

Sub foo()
Dim x As Variant
x = InputBox("Date?")
If IsDate(x) Then
For L0 = 2 To Cells.SpecialCells(xlCellTypeLastCell)
If Cells(L0, 1).Value < 1 Then
If Cells(L0, 3).Value < x Then
Cells(L0, 1).EntireRow.Interior.Color = vbYellow
End If
End If
Next
End If
End Sub
 
Why not just use data>filter>autofiler and make it automatic upon
entering a date in d1. Right click sheet tab>view code>insert this.
Use data>filter>autofilter to unfilter or a macro.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or _
Target.Address <> Range("d1").Address Then Exit Sub
Dim dDate As Date
Dim strDate As String
Dim lDate As Long
dDate = DateValue(Target)
lDate = dDate
With Range("a1:iv" & Cells(Rows.Count, 1).End(xlUp).Row)
.AutoFilter Field:=3, Criteria1:="<" & lDate, Operator:=xlAnd
.AutoFilter Field:=1, Criteria1:="<1"
End With
End Sub
 
I'm sorry to have to ask this but I modified the code slightly to fit
my spreadsheet and it keeps throwing a Type Mismatch error.
Can you tell me what above the code should look like if the following
were true?
1. YOS is actually not column A, it is column M.
2. SrvcPlus1YrMINUS90days is actually column O.
3. I'd like to not highlight the entire row even though that is what I
asked for in my first post. Instead I'd like to highlight just the
part of the row with data in it. for example A54:W54. My data actually
runs from Column A to Column W.

Thanks and sorry for not being clearer. I really appreciate your help.
 
I'm sorry to have to ask this but I modified the code slightly to fit
my spreadsheet and it keeps throwing a Type Mismatch error.
Can you tell me what above the code should look like if the following
were true?
1. YOS is actually not column A, it is column M.
2. SrvcPlus1YrMINUS90days is actually column O.
3. I'd like to not highlight the entire row even though that is what I
asked for in my first post. Instead I'd like to highlight just the
part of the row with data in it. for example A54:W54. My data actually
runs from Column A to Column W.

Thanks and sorry for not being clearer. I really appreciate your help.
Click to expand...

If?? you are talking about my worksheet change event code, the filter
columns are numbers so you just need to change the 3 and 1 to your
column NUMBERS. If all else fails, send your file with this to
dguillett1 @gmail.com
 
John said:
I'm sorry to have to ask this but I modified the code slightly to fit
my spreadsheet and it keeps throwing a Type Mismatch error.
Click to expand...

There were a few problems with what I posted before; didn't test it enough.
Those problems are fixed below and tested much more thoroughly.
Can you tell me what above the code should look like if the following
were true?
1. YOS is actually not column A, it is column M.
2. SrvcPlus1YrMINUS90days is actually column O.
3. I'd like to not highlight the entire row even though that is what I
asked for in my first post. Instead I'd like to highlight just the
part of the row with data in it. for example A54:W54. My data actually
runs from Column A to Column W.

Thanks and sorry for not being clearer. I really appreciate your help.
Click to expand...

Howsabout this?

Sub bar()
'These constants control which columns to check.
Const YOS = 13
Const SP1M90D = YOS + 2
Dim x As Variant, L0 As Long
x = InputBox("What is the report date?")
If IsDate(x) Then
For L0 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
With Range("A" & CStr(L0) & ":W" & CStr(L0)).Interior
If Cells(L0, YOS).Value < 1 Then
If Cells(L0, SP1M90D).Value < CDate(x) Then
.Color = vbYellow
Else
.Pattern = xlNone
End If
Else
.Pattern = xlNone
End If
End With
Next
End If
End Sub

The two lines that contain ".Pattern = xlNone" will remove any existing
background color from cells that don't match.

There are some good ideas from other people in this thread; might be worth
trying those, too.
 
Back
Top