Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a value and color that cell
Using Min function, I was able to find the next (soonest) date, but not able
to color the cell. I used MsgBox to see if any address was assigned to rngFound. Then used rngFound.Cells.Activate to see where the cell was, which resulted way below the list. What did I do wrong? Here is the code: Sub FindNext() Dim myRange As Range Dim answer As String Dim rngFound As Range Set myRange = Worksheets("Sheet5").Range("K2:K80") answer = Application.WorksheetFunction.Min(myRange) Set rngFound = myRange(answer) MsgBox rngFound.Address answer = Format(answer, "mm/dd/yy") MsgBox ("The next date is " & answer) rngFound.Cells.Activate rngFound.Font.ColorIndex = 3 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a value and color that cell
application.min() will result in the smallest number (including date).
For example, say 12/25/2000 was the smallest date. Range(12/25/2000) doesn't make much sense. You could try using application.match() or even .cells.find(). Dim myRng As Range Dim MinDate As Double Dim res As Variant With Worksheets("sheet5") Set myRng = .Range("K2:K80") If Application.Count(myRng) = 0 Then MsgBox "no numbers/dates in that range!" Exit Sub '??? Else MinDate = Application.Min(myRng) res = Application.Match(MinDate, myRng, 0) If IsError(res) Then MsgBox "min not found!" Else myRng(res).Font.ColorIndex = 3 End If End If End With A.S. wrote: Using Min function, I was able to find the next (soonest) date, but not able to color the cell. I used MsgBox to see if any address was assigned to rngFound. Then used rngFound.Cells.Activate to see where the cell was, which resulted way below the list. What did I do wrong? Here is the code: Sub FindNext() Dim myRange As Range Dim answer As String Dim rngFound As Range Set myRange = Worksheets("Sheet5").Range("K2:K80") answer = Application.WorksheetFunction.Min(myRange) Set rngFound = myRange(answer) MsgBox rngFound.Address answer = Format(answer, "mm/dd/yy") MsgBox ("The next date is " & answer) rngFound.Cells.Activate rngFound.Font.ColorIndex = 3 End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a value and color that cell
Try the following. Note the comments.
Sub FindNext() Dim myRange As Range Dim answer As Date Dim rngFound As Range Set myRange = Worksheets("Sheet5") _ .Range("K2:K80") answer = Application _ .WorksheetFunction.Min(myRange) Set rngFound = myRange _ .Find(What:=answer, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) MsgBox ("The next date is " & _ Format(answer, "mm/dd/yy")) 'because this code could be run when 'another worksheet is active you must 'ensure the correct worksheet is active 'before activating cells. 'Also Activate is not necessarily the same 'as Select. You can Activate a cell in 'a selected range.(It is the cell that is 'still white color.) Select a cell means 'it is the only selection Worksheets("Sheet5").Select rngFound.Cells.Activate rngFound.Font.ColorIndex = 3 End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the color the cell becomes when doing a FIND | Excel Discussion (Misc queries) | |||
Find text in cell based on color value of another cell | Excel Programming | |||
Find the next Cell with interior color using Do Until...Loop | Excel Programming | |||
.Find cell within rng when font color=3 | Excel Programming | |||
Find purple cell color, change to red | Excel Programming |