returning value based on font color-repost

[jsuden]

I have 2 spreadsheets. The one spreadsheet (called weights) has a list
of all the strategies (7 in total) in blue
font, and underneath has the underlying funds in black font, so for
example, it says


Convertible Arb (in blue)
Fund A
Fund B
Fund C
Equity LS (In blue)
Fund D
Fund E
Fund F
Fund G
Fund H
etc
on the other spreadsheet (called rets), I have all the fund names (Fund

A, Fund B, etc). I want to be able to do a lookup on the rets sheet
which will return the strategy that the fund belongs to, so basically I

need to say find this fund, look above it to teh first cell you find
that has blue font. Does anyone know an easy way to do this? Thanks for

the help!

 

[Tom Ogilvy]

This assume the rets data is in column A starting in A2
and the weights data is in column A. make appropriate adjustments.

the strategy is placed in column B of the rets sheet.

Sub UpdateData()
Dim rng as Range, rng1 as Range, rng2 as Range
Dim cell as Range, res as Variant
with worksheets("Weights")
set rng = .range(.cells(1,1),.Cells(1,1).End(xldown))
End with


with worksheets("rets")
set rng1 = .range(.cells(2,1),.Cells(2,1).End(xldown))
End with

for each cell in rng1
res = application.Match(cell,rng,0)
if not iserror(res) then
set rng2 = rng(res)
do while rng2.font.ColorIndex <> 5 and rng2.row > 1
set rng2 = rng2(0)
Loop
if rng2.font.colorIndex = 5 then
cell.offset(0,1).Value = rng2
else
cell.offset(0,1).Value = "Not identified"
end if
Next
end sub

 

[Tom Ogilvy]

Just to add:
there are several colorindex values that could be called a blue font. Check
yours and make sure it is 5 or adjust the code to match.

 

[jsuden]

thank you for your help tom...quick question though--im getting a 'next
without for' error, but if i put a second end if statemnet after the
first one, nothing happens..is there a different fix i shd be using
that you know of?

 

[jsuden]

also, if it makes it easier, instead of returning it based upon font
color, i can put a space in between the last fund and te strategy name,
so for example, i can have

strategy1
FundA
FundB
Fund C

Strategy2
Fund D
Fund E
I assume this way I could do some sort of search xlup--i tried it but
am having no luck:

Sub findstrategy()

Dim cella As Range
Dim cellb As Range
Dim i As Integer
Dim searchrange As Range
Dim Fund As String

For i = 1 To 72
Application.Goto Sheets("Rets").Range("A4")
ActiveCell.Offset(0 + i, 0).Select
Fund = ActiveCell.Value

Set searchrange = Sheets("weights").Range("AUMfunds")
Set cella = searchrange.Find(Fund, , Excel.XlFindLookIn.xlValues, ,
_
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext,
False)


For Each cellb In Worksheets("rets").Range("a:a")
cella.End(xlUp).Value = cellb.Offset(0, 2).Value
Next cellb
Next i

End Sub

 

[Tom Ogilvy]

there was a missing line. I have added that line and tested against the
data as I described it and it worked fine

Sub UpdateData()
Dim rng As Range, rng1 As Range, rng2 As Range
Dim cell As Range, res As Variant
With Worksheets("Weights")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With


With Worksheets("rets")
Set rng1 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

For Each cell In rng1
res = Application.Match(cell, rng, 0)
If Not IsError(res) Then
Set rng2 = rng(res)
Do While rng2.Font.ColorIndex <> 5 And rng2.Row > 1
Set rng2 = rng2(0)
Loop
If rng2.Font.ColorIndex = 5 Then
cell.Offset(0, 1).Value = rng2
Else
cell.Offset(0, 1).Value = "Not identified"
End If
End If
Next
End Sub