+ Reply to Thread
Results 1 to 9 of 9

Help using VBA to highlight a partial column based on text in a cell out of the range

  1. #1
    Registered User
    Join Date
    04-19-2013
    Location
    Greater London
    MS-Off Ver
    Excel 2003
    Posts
    10

    Help using VBA to highlight a partial column based on text in a cell out of the range

    Hello,
    I am trying to design a macro that will look at a text input in one cell, and then highlight a different range of cells in a column.

    For example:
    When A1 = x, A5:A25 will highlight in red

    I would then like to replicate this for other columns in a similar fashion (eg: When B1 = x, B5:B25 will highlight in red)

    I can accomplish this through the conditional formatting wizard, but unfortunately I need a macro for this job (because I will run the macro each week on a new spreadsheet).

    Many thanks for any help!

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Help using VBA to highlight a partial column based on text in a cell out of the range

    This code belongs in the worksheet module.
    Right click on the sheet tab and select View Code
    Paste the code there.

    Try it out A1,B1 any cell in Row 1, any cell in Row 1 will change that column 5:25 to Red when you type an x in it, change the value other than x and it will remove the color formatting.
    Please Login or Register  to view this content.

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Help using VBA to highlight a partial column based on text in a cell out of the range

    @davesexcel

    Hi Dave,

    Nice work, I really like it!

    If you don't mind I have added a small change.

    Please Login or Register  to view this content.
    Kind regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  4. #4
    Registered User
    Join Date
    04-19-2013
    Location
    Greater London
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Help using VBA to highlight a partial column based on text in a cell out of the range

    Thank you very much, I truly appreciate you taking the time to assist on this question. It has been very helpful and also a learning experience.

  5. #5
    Registered User
    Join Date
    04-19-2013
    Location
    Greater London
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Help using VBA to highlight a partial column based on text in a cell out of the range

    Winon, thank you very much as well. Very thankful for all the support.

  6. #6
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Help using VBA to highlight a partial column based on text in a cell out of the range

    Hello JTKEXCEL,

    You are welcome! But all credit to davesexcel who came up with a shorter Code as a solution to what I had.

    Also, thank you for the Rep.

  7. #7
    Registered User
    Join Date
    04-19-2013
    Location
    Greater London
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Help using VBA to highlight a partial column based on text in a cell out of the range

    Thanks very much to both. May I ask a quick follow-up question? If I wanted to amend the code so that the value "x" (without quotes) could appear in A3 (instead of A1 as in my original question), and still trigger the highlight in A5:25, how would I do that?

    I tried to amend it myself but it's outside of my understanding.

  8. #8
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Help using VBA to highlight a partial column based on text in a cell out of the range

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    04-19-2013
    Location
    Greater London
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Help using VBA to highlight a partial column based on text in a cell out of the range

    Awesome, thanks very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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