+ Reply to Thread
Results 1 to 8 of 8

Can't find VBA code to format a column based on text within a row

  1. #1
    Registered User
    Join Date
    04-19-2020
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 365
    Posts
    9

    Can't find VBA code to format a column based on text within a row

    So, I've got this report macro, currently works fine, but I'm looking at including a quality of life improvement to it.

    What I'm after is something along the same lines as this
    Format column with cell containing "Employee No" to custom format "000000"

    I feel like this is a simple thing, but I'm relatively new to VBA and exploring how deep excel can go.

    I can't upload any documents as an example as they all contain confidential information.
    Last edited by LuciferUntamed; 04-26-2020 at 08:47 PM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Arrow Re: Can't find VBA code to format a column based on text within a row


    So activate the Macro Recorder and operate manually : you will get your own code base
    or just read the VBA help of the NumberFormat property.
    Marc Hell

  3. #3
    Registered User
    Join Date
    04-19-2020
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Can't find VBA code to format a column based on text within a row

    The thing is, The column isn't always the same, and I'm new to VBA. Like, I only just started looking at it 2 weeks ago. The Macro that is currently in use, wasn't created by me. I'm just trying to add a little quality of life to it.
    The Row is always the same, but the column isn't. The text I'm looking for is "Employee No". Then I need to format that column to a six digit number ie. "000316"
    Sorry, I probably seem like the dumbest person.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Arrow Re: Can't find VBA code to format a column based on text within a row


    So post at least the code and a smart enough description of your data worksheet in order there is nothing to guess
    or as it's easy to create & attach a sample workbook with dummy data but respecting the original layout

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,613

    Re: Can't find VBA code to format a column based on text within a row

    Just a guess but this might get you started.

    It looks for text on row 1, when foud that column gets formatted.

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  6. #6
    Registered User
    Join Date
    04-19-2020
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Can't find VBA code to format a column based on text within a row

    Thank you,

    I feel like that's close, although it didn't work.

    I've attached a dummy sheet to what it looks like after the macro has run, before the macro has run, it's in row 1.

    Row 6 contains an employee number at the length it should be.
    In Cell H3 I've put an employee number as it should be with the 0's preceding

    Should I be posting the Macro's code here too? only reason I haven't been is because it is lengthy.
    Attached Files Attached Files

  7. #7
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,613

    Re: Can't find VBA code to format a column based on text within a row

    Please Login or Register  to view this content.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,052

    Re: Can't find VBA code to format a column based on text within a row

    If the column headers are always in row 3 then use a conditional formatting formula:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] In a specific column find text and change the format
    By Louisa Venter in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-22-2015, 02:43 PM
  2. vba code to autofilter column based on conditional format
    By krazyhype19 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-28-2014, 03:05 AM
  3. VBA Code to Find Specified text within a Column and perform Text to Column
    By MHamid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-26-2014, 06:42 AM
  4. InStr function doesn't find specific text, but finds any text in column and runs code
    By mikey3580 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-27-2014, 03:50 AM
  5. VB Code to find 1st Occurrence of text in column A
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-25-2013, 09:46 AM
  6. [SOLVED] Find last value in one column based on values in another (text only)
    By slash_gnr3k in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-05-2012, 11:37 AM
  7. Find column number based on text
    By pikapika13 in forum Excel General
    Replies: 1
    Last Post: 05-09-2005, 05:37 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1