Referencing a Range of Cells with Named Columns and Target.Row

  • I'm currently working on a spreadsheet with various different macros in it. The current macro I'm having problems with is a Private Sub on a Worksheet which is called on any Change on the worksheet.

    What I am trying to achieve is depending on the value in the column named "Status" the entire row will have its background changed to either white or blue. There are a couple of columns I want to leave black as they are code generated cells so I've split this into 3 blocks of code. One hiccup I had with this was that the grid lines on the line effected vanished so I've had to add in code to actually put in proper grid lines rather than the default grey ones that you get on a blank sheet.

    All is good at this point with the code using lettered references for the columns A, B, C, etc. but I'm trying to use defined column names throughout the code so that if a user adds a column into the spreadsheet at any point the macros will still run correctly and I can't seem to get this working for this section of code.

    Below is the section of code I've managed to get working:

    What I'm trying to do is change the first Range lines to reference my defined columns names along the lines of:

    Range("Name" & Target.Row & ":Comments" & Target.Row).Interior.ColorIndex = 2

    The other headache I've got with this code is that if the cell "Status", Target.Row is changed by another sheet within the workbook (There are two sheets, one with basic information and another with more in depth information and common columns on both sheets that are copied onto the other sheet so that both sheets have the correct information) then I start getting "Run-Time error 1004: Select method of Range class failed" messages.

    Any help with this would be much appreciated!

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    A selection error is often times caused by the fact that you can't select a range on a sheet if that sheet is not the active sheet.

    Using activate and select is almost never required and is inefficient coding.

    Just refer to the sheet and range and you don't have to activate the sheet or select the range.

    This code:

    Sheet4.Range("A" & Target.Row & ":AB" & Target.Row).Select      Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    Can be written this way.

    With Sheet4.Range("A" & Target.Row & ":AB" & Target.Row)
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
    End With

    You can be on any sheet and this code will run against the sheet and range referred to in the code, without activating the sheet or selecting the range.

    Think of it this way, what's faster thinking to yourself I'd like to bold the font in cell A1 on a sheet I'm not looking at, and have it just happen, or activating the sheet, selecting the cell and choosing bold from the menu.

    Writing the code to just do what you want is like thinking about it and having it happen as opposed to jumping from sheet to sheet range to range and selecting this and that and clicking on menu choices with your mouse.

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    Cheers skywriter, that's worked a treat. Now all I've got to figure out is how to utilise column names rather than letters and jobs a good un!

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    Hey Dave,

    This is what I do. I always check that a sheet is available, find the used range parameters, then check that the column headers are present and assign a variable. I usually name the variable the sheetname it refers to, then the column header. I find this much easier to use. For example, to find Application Number in the first row on the data sheet...

    Dim DataApplicationNumber as Integer
    DataApplicationNumber = 0
    On Error Resume Next
    DataApplicationNumber = Application.WorksheetFunction.Match("Application Number", Range(ThisWorkbook.Sheets("Data").Cells(1, 1), ThisWorkbook.Sheets("Data").Cells(1, DataLstCl)), 0)
    On Error GoTo 0
    If DataApplicationNumber = "0" Then
    MsgBox "I cannot find 'Application Number' within  tab. Please add in"
    End If

    You can then use this like this:-

    ThisWorkbook.Sheets("Summary").Cells(1, 1).Value = ThisWorkbook.Sheets("Data").Cells(1, DataApplicationNumber).Value

    And build up what you need.... Hope that helps

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    Thanks M40wen but unfortunately I don't think that will help me unless I'm misunderstanding it. On my sheet all the columns are already named specific to their purpose but end users may require to add additional columns in for other information not normally required / available and I don't want this addition of columns to cause problems with any of the macros on my sheet. As a result all of the macros are written using the predetermined column names meaning they will all run on the appropriate columns no matter where or how many columns a user adds.

    The only bit of the macro I can't get working like this is the section shown above where I'm having to reference column letters instead.

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    No it will still work - I design it that way specifically for the problem you mention. The logic is that you are declaring the column header variable, if not found (because it has been deleted or renamed) you can write an error message, or you could default it in. Else, the variable is assigned the column position in your spreadsheet. So if a user added a column, when the code next runs it will search for the header again and assign the new position. If you assign all the column headers this way then it wont matter where users add or move (though deleting header name will yield an error message box - again you can code or restrict users from amending the column headers).

    The problem you have with your above logic is that you are specifying fixed ranges but you are allowing users to potentially move things round- ie column A through to S. If a user insert a column at say C, should the colouring range be an additional column (A to T), or does it depend in which case should you actually colour on an individual basis ie the orginal columns A to S should be coloured, but the new column C should not be. This is slower, but might be more accurate depending on what you are trying to do.

    Hope that makes sense. Any probs let me know.

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    Thanks M40wen, that makes a bit more sense. My problem isn't so much giving the column names (as I've already done that manually) but how to then use those names in the code to colour and add grid lines. As you say, I want A to S coloured and gridded and if someone adds a column at C then it would become A to T that are coloured and gridded. I can now see how your code would generate the column names (I think) but can't see how it would help with the colouring and grid lining of the row between those columns.

    For example I have the code

    If Not Intersect(Target, Range("Mech_Work_Parent")) Is Nothing Then
            Sheet11.Range("Mech_Tag_Parent").Cells(Target.Row, 1) = Sheet12.Range("Mech_Work_Parent").Cells(Target.Row, 1) 'Copy Parent Tag
        End If

    to copy the content of a cell on one sheet to another sheet if it is updated (I suspect the Sheet references are probably not required but it's working so I don't want to tinker too much and risk breaking it) with the column names Mech_Tag_Parent and Mech_Work_Parent declared with a scope of Workbook. This way if any columns are inserted into the separate sheets then it won't make any difference to the above code. What I'm trying to do (without much success!) is use these same predefined names to limit the range of cells which are coloured and having grid lines added.

    What in effect I want to do is change the code

    Range("A6:T6").Interior.ColourIndex = 2

    to something like

    Range("Name" & Target.Row & ":Comments" & Target.Row).Interior.ColorIndex = 2

    but I can't figure out how to get this to work

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    You are using named ranges, but this might be a better alternative...

    Ok, so how about we "assume" the range anchors will start in cell "A1" with the header column "Start" ( variable - HeaderNameA) and cell "S1" header column called "End" ( variable - HeaderNameS).

    By finding the start and end points, everything else becomes immaterial. The problem of course is users will do their best to not follow convention iewhat if they move the header names round so the "end" column is now at start etc (in that example you use min and max but my point is good design limits what the user can mess up for you, great design lets them and code handles it).

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    "Name" and "Comments" are just two of multiple headings on the sheet. The sheet is a project register which is taking a list of equipment being installed as part of a project including document references, ranges, set points, etc. of which the tag number of the equipment ("Name") is the first column and the final column is a comments one. The majority of the columns have names assigned and then a macro is being run at the end of the project to put the data into a format ready for a bulk upload to SAP. During the life cycle of the project there may be a requirement for engineers to add an additional column in for any extra data they want to keep handy that is not required in SAP and can't be predicted to enable the additional columns to be added into the template that I am creating.

    What the small section of the code referenced above is doing is looking at the value in one cell which is provided by a drop down box and changing the background colour of the entire row dependant on that value. Since this makes the default grey grid lines vanish I'm also adding in grid lines as part of the process.

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    M40wen, the second last row of your code is along the lines of what I'm looking for but ideally I want to use "Name" and "Comments" instead of HeaderNameA and HeaderNameB.

    The problem is that when I use

    Range(Sheet4.Cells(Target.Row, "Name"), Sheet4.Cells(Target.Row, "Comments")).Interior.ColorIndex = 2

    I get a Run-time error '13' Type mismatch error

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    And thanks for the advice on a better way to deal with headers in general but this spreadsheet is rather large and has taken a couple of months to develop so I'm not very keen on completely changing the way I'm dealing with the headers on this one if I can avoid it but will bare it in mind for future!

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    One sec, is the named range "Name" and "Comments", or is that the column header name?

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    Name and comments are variables so you don't use "".

    Hopefully that gets you on the right track

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    Name and Comments are what I've called the columns. To give you an idea of the scope of the spreadsheet I've got over 200 named columns over 11 sheets of which Name and Comments are just two of the Column Names. For example Name refers to Sheet4!$A:$A

    If I remove the "" I'm getting an error message saying Run-time error '1004': Application-defined or object-defined error

  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    Range(Cells(, (Range("Names").Columns.Address(False, False))).Address, Cells(, (Range("Comments").Columns.Address(False, False))).Address).Interior.ColorIndex = 2
  • Re: Referencing a Range of Cells with Named Columns and Target.Row

    Thanks everyone. M40wen, unfortunately that was giving me the following error: Run-time error '438': Object doesn't support this property or method. It did however point me in the right direction as I hadn't come across the .address property before. I've now managed to get the following code working.

    Range((Range("Names").Cells(Target.Row, 1).Address) & ":" & (Range("Comments").Cells(Target.Row, 1).Address)).Interior.ColorIndex = 2

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!