Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel - return column name in range

    excel 97

    I'm unfamiliar with excel & I would like to do the following logic:

    My range is rows - Range ("D4:AG4") which have dates stored in column.
    Obtain todays date & search range.
    When found highlight the column.

    The problem is I dont know the syntax to highlight the current column where search is found.
    Does anyone know the syntax?
    TIA Diana

  2. #2
    ralph_davies
    Guest

    Re: Excel - return column name in range

    Useful Tip.
    I have found one of the easiest ways to find correct syntax is to record a macro and have a look.
    You can often find the correct syntax by recording a macro.
    select tools macro record new macro,
    then just do what you want by hand then take a look at what is produced.
    This is often better than using VB help etc.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Excel - return column name in range

    do you mean something like Range("G4").EntireColumn.Select,

    or rg.EntireColumn.Select

    if rg is a range variable returned by a search?

    Jeremy

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - return column name in range

    Jeremy

    Yes I've done a search in a certain range being a row
    when found - highlight the entire Column.
    I've recorded macro & gone to VB help without much success.

    For example the search was found in the range 'Q4' - & the column is 'Q' - I want to return column name & then highlight entire column.

    Diana

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Excel - return column name in range

    Sorry, Diana I misread your post and assumed you'd written the Find from scratch. You need some code that will execute a Find and return the cell where the match is found. Try this:

    dim c as range

    set c = rows("3:3").Find("fred", lookin:=xlValues)
    if not c is nothing then
    msgbox c.column
    c.entirecolumn.select
    end if

    This looks for the string "fred" in row 3 and tests to see whether anything was found, then returns the column number and highlights the column.

    If you need the column name, then you must parse c.Address to get the column letter(s).

    Jeremy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •