Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning value based on font color-repost
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning value based on font color-repost
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 -- Regards, Tom Ogilvy " wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning value based on font color-repost
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. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy " wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning value based on font color-repost
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 wrote: 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 -- Regards, Tom Ogilvy " wrote: 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning value based on font color-repost
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 -- Regards, Tom Ogilvy wrote in message oups.com... 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 wrote: 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 wrote: 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 -- Regards, Tom Ogilvy " wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Font color based on font type or size | Excel Discussion (Misc queries) | |||
change font color based on a value | Excel Worksheet Functions | |||
Using Find in VBA based on font color | Excel Programming | |||
Sum based on font color | Excel Programming | |||
Sum a row of data based on FONT COLOR ( | Excel Programming |