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!

 

[Guest]

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

 

[Guest]

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?

 

[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