tf37
Board Regular
- Joined
- Apr 16, 2004
- Messages
- 169
Gang, not sure what else to call this, and I cannot upload an image of the worksheet to help show it.
I have a worksheet that has data in columns B - I and rows 2 - 13
Column B contains the years in each row, (B2=2014) (B3=2015) etc...each row is the next year in sequence
Column I contains a year, but 2014 may appear only in rows 10 & 12 in column I
So I used conditional formatting to highlight the cells that match and recorded a macro like this:
Range("A2:K14").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="2014", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535 ' yellow
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
that works fine.
Now...column C will contain a value
Column K will contain a value and is associated with column I
Meaning...if column I = 2014 and matches column B year of 2014, then I need to take the data in column K and subtract that from column C
I've tried using Index & Match to help select the columns and rows that match, but not having much luck.
' Need to figure out now how to select cell F2 and have it provide the balance of
' C2 minus those in range K2:K13 that match range I2:I13
' if b2 = yellow, and I2:I13 are yellow, use the figure in range K2:K13 that are yellow in range I2:I13
' and subtract those figures from C2 and enter that data if F2
' this would repeat for each year range in B2:B13
' Columns I & K will continue to expand downwards, so rather than stop at I13, or K13, have it run out to perhaps I30 & K30
I can upload the entire worksheet to my website, or drop box perhaps, and provide better explanations of what I'm trying to do here.
If it isn't possible due to the data being scatted in column I and a year of 2014, 2015 etc., appearing in multiple rows not in line with column B2 then I need to figure out a whole new way
Column B will contain the years 2014-2024 in B2 - B12
Column I represents the years that funds were used and column K the amount of the funds used
Column C2 matches the years column B, but is the total awarded
Take column I that matches, and the amount awarded in column K (matching rows) and subtract from column C (say b2) and report that amount in F2
Thanks gang for even looking at this, and I know my posting is perhaps confusing.
Tell me where and how to upload a worksheet, and my code thus far and what I'm wanting...and I'll be more than happy to
Thanks again
I have a worksheet that has data in columns B - I and rows 2 - 13
Column B contains the years in each row, (B2=2014) (B3=2015) etc...each row is the next year in sequence
Column I contains a year, but 2014 may appear only in rows 10 & 12 in column I
So I used conditional formatting to highlight the cells that match and recorded a macro like this:
Range("A2:K14").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="2014", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535 ' yellow
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
that works fine.
Now...column C will contain a value
Column K will contain a value and is associated with column I
Meaning...if column I = 2014 and matches column B year of 2014, then I need to take the data in column K and subtract that from column C
I've tried using Index & Match to help select the columns and rows that match, but not having much luck.
' Need to figure out now how to select cell F2 and have it provide the balance of
' C2 minus those in range K2:K13 that match range I2:I13
' if b2 = yellow, and I2:I13 are yellow, use the figure in range K2:K13 that are yellow in range I2:I13
' and subtract those figures from C2 and enter that data if F2
' this would repeat for each year range in B2:B13
' Columns I & K will continue to expand downwards, so rather than stop at I13, or K13, have it run out to perhaps I30 & K30
I can upload the entire worksheet to my website, or drop box perhaps, and provide better explanations of what I'm trying to do here.
If it isn't possible due to the data being scatted in column I and a year of 2014, 2015 etc., appearing in multiple rows not in line with column B2 then I need to figure out a whole new way
Column B will contain the years 2014-2024 in B2 - B12
Column I represents the years that funds were used and column K the amount of the funds used
Column C2 matches the years column B, but is the total awarded
Take column I that matches, and the amount awarded in column K (matching rows) and subtract from column C (say b2) and report that amount in F2
Thanks gang for even looking at this, and I know my posting is perhaps confusing.
Tell me where and how to upload a worksheet, and my code thus far and what I'm wanting...and I'll be more than happy to
Thanks again