Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find cell within rng when font color=3
I have a loop that looks at each cell and if color is 3 then place cell value
in variable. I need to speed up the process. There is only one cell that meets the criteria just hate look at all cells. Tried many ways nothing working. Please help. -- Jim at Eagle |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find cell within rng when font color=3
What version of excel are you using?
xl2002 (IIRC) has the ability to search for formats. If you're using a newer version of excel, you can record a macro when you do the edit|Find manually. If you're using an older version of excel, I think you'll be looping. Jim at Eagle wrote: I have a loop that looks at each cell and if color is 3 then place cell value in variable. I need to speed up the process. There is only one cell that meets the criteria just hate look at all cells. Tried many ways nothing working. Please help. -- Jim at Eagle -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find cell within rng when font color=3
Thanks for your help got this and no errors.
Sub FindColor() With Application.FindFormat.Font .ColorIndex = 3 End With End Sub But: I need to limit the search to a range of cells and retrieve and store the find in a variable. -- Jim at Eagle "Dave Peterson" wrote: What version of excel are you using? xl2002 (IIRC) has the ability to search for formats. If you're using a newer version of excel, you can record a macro when you do the edit|Find manually. If you're using an older version of excel, I think you'll be looping. Jim at Eagle wrote: I have a loop that looks at each cell and if color is 3 then place cell value in variable. I need to speed up the process. There is only one cell that meets the criteria just hate look at all cells. Tried many ways nothing working. Please help. -- Jim at Eagle -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find cell within rng when font color=3
Select your range while you're recording:
I got this: Columns("C:C").Select With Application.FindFormat.Font .ColorIndex = 3 End With Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=True).Activate I'd edit it to: Option Explicit Sub testme() Dim myRng As Range Dim FoundCell As Range Dim myVar As Variant With Worksheets("Sheet1") Set myRng = .Range("c1").EntireColumn End With With Application.FindFormat .Clear .Font.ColorIndex = 3 End With With myRng Set FoundCell = .Cells.Find(What:="", _ After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=True) End With If FoundCell Is Nothing Then MsgBox "Not found!" Else myVar = FoundCell.Value MsgBox myVar End If End Sub Jim at Eagle wrote: Thanks for your help got this and no errors. Sub FindColor() With Application.FindFormat.Font .ColorIndex = 3 End With End Sub But: I need to limit the search to a range of cells and retrieve and store the find in a variable. -- Jim at Eagle "Dave Peterson" wrote: What version of excel are you using? xl2002 (IIRC) has the ability to search for formats. If you're using a newer version of excel, you can record a macro when you do the edit|Find manually. If you're using an older version of excel, I think you'll be looping. Jim at Eagle wrote: I have a loop that looks at each cell and if color is 3 then place cell value in variable. I need to speed up the process. There is only one cell that meets the criteria just hate look at all cells. Tried many ways nothing working. Please help. -- Jim at Eagle -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot change font color or cell color | Excel Discussion (Misc queries) | |||
Cell and font color | Excel Programming | |||
Using Find in VBA based on font color | Excel Programming | |||
How to change the default Border, Font Color, and Cell Color | Excel Discussion (Misc queries) | |||
Default Border, Font Color, and Cell Background Color | Excel Discussion (Misc queries) |