Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find then highlight in yellow
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find then highlight in yellow
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find then highlight in yellow
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 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 -- Watch the knife. The stabby part is facing you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find then highlight in yellow
Why not just use datafilterautofiler and make it automatic upon
entering a date in d1. Right click sheet tabview codeinsert this. Use datafilterautofilter 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 On Jan 9, 7:25*pm, 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find then highlight in yellow
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find then highlight in yellow
On Jan 10, 8:59*am, John Menken wrote:
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. 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find then highlight in yellow
John Menken wrote:
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. 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. 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. -- Words and ideas are what change our world. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Yellow error code | Excel Programming | |||
Highlight a row yellow if a cell contains TRUE | Excel Discussion (Misc queries) | |||
Find rows with a common item and find or highlight difference | Excel Programming | |||
find and highlight | Excel Discussion (Misc queries) | |||
formula for if Cell c1 is yellow shaded cell d1 should be yellow | Excel Discussion (Misc queries) |