+ Reply to Thread
Results 1 to 14 of 14

Need routine to color cols based on value in Match col cell

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Need routine to color cols based on value in Match col cell

    Enclosed is an example file. In it....i have several cols. The cols which contain the text "Match".....look at these cols. In the Match col where it has a value of "No" i want to highlight this cell and the two previous cells in that same row.

    see enclosed file as an example.

    not sure how to program this using vba.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Need routine to color cols based on value in Match col cell

    Hi welchs101,

    Try this:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Need routine to color cols based on value in Match col cell

    Add conditional formatting, so once you run the code, no need to run again unless you change the heading.
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Need routine to color cols based on value in Match col cell

    jindon, i am looking at yours more closely because i think you said something about running it only once which is what i was hoping to do.

    But, your code is kinda advanced and i dont know exactly what its doing.

    Can you tell me what this means
    (1).FormatConditions.Delete
    (2) And can you explain this just a little more
    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Need routine to color cols based on value in Match col cell

    1) Delete all Conditional Formatting

    2) When header starts with "Match", it adds conditional formatting.
    e.g.
    If col.C has "Match*", refers to Col.A:C, (.Columns(ii).Offset(, -2).Resize(, 3))

    You can see all the details for CF conditions added [Conditional Fromatting]

  6. #6
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Need routine to color cols based on value in Match col cell

    jindon,
    i have probably learned more from you and your code on how to do things than anyone else on here...so thanks.

    What i came up with is enclosed. i use a dictonary to capture the cols i want to look at and then use this in a subsequent for-loop to go through each of the rows.

    I still suspect your method is "faster" though...........

    as you probably guessed my test file is just that a test file..........the actual file is much larger has ~10k rows and ~ 48 cols.

    i am still trying to understand the conditional formatting stuff............

    enclosed is what i came up with
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Need routine to color cols based on value in Match col cell

    one more thing........

    what does this mean exactly
    Please Login or Register  to view this content.
    anything i can read on this that you know of?

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Need routine to color cols based on value in Match col cell

    You are making things too complex....

    There will be few more method to do

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Need routine to color cols based on value in Match col cell

    Quote Originally Posted by welchs101 View Post
    one more thing........

    what does this mean exactly
    Please Login or Register  to view this content.
    1st arg Type: = xlExpressions (constant = 2)
    2nd arg Operator, not required.
    3rd arg Formula1 : = "=" & .Cells(1, 3).Address(False, True) & "=""No"""

    See more details
    https://docs.microsoft.com/en-us/off...conditions.add

  10. #10
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Need routine to color cols based on value in Match col cell

    jindon,

    the 2nd set of code (see below)

    Please Login or Register  to view this content.

    is much faster for some reason. Using a "real" file my code takes about 10sec to run.........using the code above takes yours about 0.1sec to run.........

  11. #11
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Need routine to color cols based on value in Match col cell

    jindon,

    consider this code you sent:
    Please Login or Register  to view this content.
    i understand the first part.......its looking for the headers which contain the text "Match". But i dont understand the 2nd part and i dont understand the "*" between the first and 2nd part?

  12. #12
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Need routine to color cols based on value in Match col cell

    i cant seem to find any information on the internet about the syntax for
    "Application.Match"

  13. #13
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Need routine to color cols based on value in Match col cell

    ok....for those trying to find info on application.match just lookup worksheetfunction.match

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Need routine to color cols based on value in Match col cell

    Just FYI,

    WorksheetFunction.Match method and Application.Match method works slightly different.

    Both finds matching index within the range/array, but when no match found, WorksheetFunction raises Run-Time error, whereas Application returns Error Value.

    So, Error trapping should be different.

    AFAIK, Application with sheet function is inherited to keep compatibility with xl95, since WrksheetFunction was instroduced from xl97.

+ 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. Replies: 9
    Last Post: 11-17-2011, 12:58 PM
  2. [SOLVED] Cond Format:re color 2 cols, skip 2 cols
    By kk in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  3. [SOLVED] mod(row):Cond Format:re color 2 cols, skip 2 cols
    By Tat in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  4. [SOLVED] Cond Format:re color 2 cols, skip 2 cols
    By Tat in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-22-2005, 02:05 PM

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