Hi Howard, It's late here, but you have at least one problem. In the following line:
mycolor = cBWTStop.Cells(ActiveCell.Row, ActiveCell.Column).Font.Color you reference the activecell.row and .column; but that gives you the row and column for the active cell relative to the sheet as a whole. cBWTStop.Cells, though, needs a row and column specified relative to cBWTStop. In other words, cBWTStop.Cells(2,1) refers to the first data item in that column. The row happens in this case to be the same, since CBWTStop begins in the first row of your worksheet, but since cBWTStop is only one column, you should specify 1 for the column. This should work: mycolor = cBWTStop.Cells(ActiveCell.Row, 1).Font.Color This would be a more general solution in case cBWTStop didn't start at row 1 on your worksheet: mycolor = cBWTStop.Cells(ActiveCell.Row - cBWTStop.Cells(1).Row + 1, 1).Font.Color Or using Application.Intersect you can simplify it like this: mycolor = Application.Intersect(ActiveCell.EntireRow, cBWTStop).Font.Color When I get a chance, I will still try to respond to your previous messages. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of tangledweb Sent: Thursday, March 22, 2012 12:20 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Need help with VBAfor Excel naming and using column names for looping Asa, I tried what you said but am doing something wrong. First when I used the exact same string as both the Set variable and the Match variable it said it needed an object. It did not like identical names I guess? Then I created different object names and that would compile but I have tried several variations to get a correct simple value returned (what I really want to do will be more complex but I have to crack this fundamental first) and all get errors of different types. It may be that what I really need to do is remove the .Columns from your expression and then the variable would become an integer and I could use it as an index? But even if that is true and what I decide to do I would still like to understand what is wrong here. I have a set of columns and am just trying to get the color value of one of them out of the range of the column BWTStop which should be the object cBWTStop I think? ActiveCell in these attempts was the cell just to the right of one of the colored cells. The current uncommented version keeps returning 0 but the actual color value of the cell I want is 255. The commented out versions get runtime errors or compile errors. Sub Macro1() ' ' Macro1 Macro which sets the column names for the ChartData sheet ' ' Column names Dim BarDate As String Dim BarTime As String Dim Baropen As String Dim Barhigh As String Dim Barlow As String Dim Barclose As String Dim BWTStop As String Dim Short_Long_Cash As String ' Column reference object Dim cBarDate As Object Dim cBarTime As Object Dim cBaropen As Object Dim cBarhigh As Object Dim cBarlow As Object Dim cBarclose As Object Dim cBWTStop As Object Dim cShort_Long_Cash As Object With Sheets("ChartData").UsedRange 'All the columns will only have the used range. Set cBarDate = .Columns(WorksheetFunction.Match("BarDate", .Rows(1), 0)) Set cBarTime = .Columns(WorksheetFunction.Match("BarTime", .Rows(1), 0)) Set cBaropen = .Columns(WorksheetFunction.Match("BarOpen", .Rows(1), 0)) Set cBarhigh = .Columns(WorksheetFunction.Match("BarHigh", .Rows(1), 0)) Set cBarlow = .Columns(WorksheetFunction.Match("BarLow", .Rows(1), 0)) Set cBarclose = .Columns(WorksheetFunction.Match("BarClose", .Rows(1), 0)) Set cBWTStop = .Columns(WorksheetFunction.Match("BWTStop", .Rows(1), 0)) Set cShort_Long_Cash = .Columns(WorksheetFunction.Match("Short_Long_Cash", .Rows(1), 0)) End With ' ' With Selection.Font Dim mycolor ' mycolor = cBWTStop(Cells(ActiveCell.Offset(ActiveCell.Row - 1), ActiveCell.col)).Font.Color ' mycolor = Cells(4, cBWTStop).Font.Color mycolor = cBWTStop.Cells(ActiveCell.Row, ActiveCell.Column).Font.Color Debug.Print mycolor -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
