1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel VBA Formula for Conditional Formatting

Discussion in 'Business Applications' started by 566290, Mar 13, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. 566290

    566290 Thread Starter

    Joined:
    Jan 30, 2008
    Messages:
    19
    I am trying to find a macro formula using conditional formatting to separate names on an excel worksheet with a black line. This is a worksheet that will have new information put into it every month, approximately 600 new lines of data each time. This is data for employees, so it will have the employee name then by month, up to 12 months of data for each employee and 78 columns for each month. Each month the line will need to be changed because there is one more line of information.

    The condition would be in Column E when =$E3<>$E4 (employee number)then a black line would be placed on the bottom of the cell from A:BZ, first to last column of data.

    I am using Excel 2003 and I have other conditional formatting in the majority of the cells, so I can't use another conditional format from the tools menu because it will not place two in the same cell. That is why I am looking for a macro that may accomplish the same thing.

    If there is another way to accomplish this that would be great. After 2 months of data I have 1200 lines of data.

    I tried to attach a sample of the document to this but it would not allow me to.

    Thanks,
     
  2. exegete

    exegete

    Joined:
    Oct 26, 2005
    Messages:
    354
    I think that you will have to incorporate the other conditions into VBA. Otherwise they will conflict.
     
  3. 566290

    566290 Thread Starter

    Joined:
    Jan 30, 2008
    Messages:
    19
    I don't know how to write a VBA macro for all of that.
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Macros use a language similar to VBA but not quite. I am sure one of the VBA gurus here can help. Let me see if one can pop in....
     
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Post a sample workbook with some dummy data so we can see the data layout.

    I'm still kinda confused what the end goal is here. If you want to limit the records for a particular employee you can simply use the autofilter to display specific results. Is there any particular reason why you want all the records to appear in the same sheet seperated by a line?

    Regards,
    Rollin
     
  6. exegete

    exegete

    Joined:
    Oct 26, 2005
    Messages:
    354
    Or if that is the case, then use Scenarios, and code to restrict who sees what.
     
  7. exegete

    exegete

    Joined:
    Oct 26, 2005
    Messages:
    354
    In Excel, in distinction to Access, Macros are a set of VBA code lines to make a unit that does something. So, for the purposes here, VBA and macros for Excel are essentially the same, and identical in terms of language.
    :D
     
  8. 566290

    566290 Thread Starter

    Joined:
    Jan 30, 2008
    Messages:
    19
    The end result is to have information for a manager with data for each agent on their team by month. The managers want to be able to see their team as a whole, but because there is so much data it would be visually easier for them if the names and all columns of data were separated by a line. There will be instances where they will use the auto filter when then they want to look at a single agent, but when they are looking at their entire team, up to 45 people, each having up to 12 months of data, they would like a visual separation between the names.
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    As Rollin said, "Post a sample workbook with some dummy data so we can see the data layout."; guessing at your layout is pretty much pointless.
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    The obvious question is why is this not being done in MS Access or another database? If you really want this done in Excel post your sample workbook and we'll write a macro for you.

    Regards,
    Rollin
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    As the others said, post, please. Do you have a column in your spreadsheet that differentiates on month from the next? For example, you could have a column that said Jan (or 01) for all the pertinent rows, then another that said Feb (or 02) for all the pertinent rows, etc. Such a column would make it easy to insert rows based on where the data changes. I have code that inserts a page break at such points - it could be easily modified.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/692932

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice