+ Reply to Thread
Results 1 to 12 of 12

VBA macro code needed to highlight rows in a table given a specfic text in one column

  1. #1
    Registered User
    Join Date
    02-27-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    6

    VBA macro code needed to highlight rows in a table given a specfic text in one column

    Hi everyone, I'm new here.

    I work with excel everyday, but sadly I dont know that much about VBA coding

    Current I'm working with data in a table that looks like this:

    A B C D E F G H I J
    1
    2
    3
    4
    5


    Column H has some text in it. The text that the code needs to look for is either "NET" or "RET". Based on which text string is in each row of column H it needs to then highlight that entire row inside the table with a given color. (at this point the color can be anything).

    So essentially the code needs to check each row of column H inside the table for the text "NET" or "RET" and if found needs to color that row with a given color.


    Thanks all. I tried earlier to do this with conditional formatting but I couldnt find any way to make it happen - but it may be possible using that method too?

    Cheers,
    Eventful

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: VBA macro code needed to highlight rows in a table given a specfic text in one column

    Place the cursor in 2ND ROW and select the range which needs to be highlighted.

    In Conditional Formatting formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Apply the desired highlighting color.

    The formula refers to 2nd row so active cell should be in 2nd row of the selection.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: VBA macro code needed to highlight rows in a table given a specfic text in one column

    Or if you want macro.
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    02-27-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: VBA macro code needed to highlight rows in a table given a specfic text in one column

    Thanks guys. I went with the vb code. worked well.

    Last question, is it possible to assign shortcut keys into the code so that I can copy paste the cb code across multiple documents, then just quickly hit a shortcut ket instead of going through the macro menu?

    Cheers

  5. #5
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: VBA macro code needed to highlight rows in a table given a specfic text in one column

    Select the macro from View-->Macros-->View Macros
    In the Macro dialog box, click on HiliRow and click on options
    Enter your key (Combination of Ctrl and/or Shift and any other key is possible) (Make sure you are not using any keys used by Excel by default)

  6. #6
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: VBA macro code needed to highlight rows in a table given a specfic text in one column

    Select the macro from View-->Macros-->View Macros
    In the Macro dialog box, click on HiliRow and click on options
    Enter your key (Combination of Ctrl and/or Shift and any other key is possible) (Make sure you are not using any keys used by Excel by default)

  7. #7
    Registered User
    Join Date
    01-22-2013
    Location
    Hawaii
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: VBA macro code needed to highlight rows in a table given a specfic text in one column

    I came across this and can probably use it, but need some additional clarification. When you put ''Select which cells you want the macro to run" Can you please clarify format? I have 7 worksheets that contain the same information, but for different people.

  8. #8
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: VBA macro code needed to highlight rows in a table given a specfic text in one column

    Kre30a: You will have to run macro for each sheet. In each sheet, select all the cells that you want the macro to check for the words and run the macro.

  9. #9
    Registered User
    Join Date
    01-22-2013
    Location
    Hawaii
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: VBA macro code needed to highlight rows in a table given a specfic text in one column

    Okay Here is the VBA based on what you stated:

    Sub HiLiRow()
    For Each cl In Selection("A1:W1434")
    If cl.Value = "711-Billable" Then
    cl.EntireRow.Interior.Color = RGB(255, 0, 0)
    End If
    Next cl
    End Sub


    I run this as a Macro and get the error "Type Mismatch"

  10. #10
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: VBA macro code needed to highlight rows in a table given a specfic text in one column

    Hi,

    You just need to select the cells before running the macro. No need to enter the Range next to selection.
    For Each cl In Selection
    That's all, no need of ("A1:...

  11. #11
    Registered User
    Join Date
    01-22-2013
    Location
    Hawaii
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: VBA macro code needed to highlight rows in a table given a specfic text in one column

    Got it, I'm a dope!

  12. #12
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: VBA macro code needed to highlight rows in a table given a specfic text in one column

    Fine. Next time on wards, if you have a query, please start a new thread instead of hijacking other's threads. If you feel this is relevant for your thread, you can paste a link to this thread in your post.

+ 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