VBA Macro - Highlight Row for Additions OR Cell for Changes

  • Hello all!


    Okay, I have a template for another department I am developing. I need to add some conditional formatting via VBA. I managed to find a code on the forums and modified it. It meets one of my two requirements as is, but there is 1 change I would like (the range). I just either need a second code or modification to this code. I am still very new to Worksheet_Change coding. The code I found as works for "part 2 is below"


    Part 1 - Any NEW entry will require the entire row to be highlighted (I recommend in either orange or yellow... no preference).
    - The range of the data is columns A - DD
    - The number of rows will vary from week to week as this is a global workbook (so I would rather not be required to list a specific range)
    - The key cell that would indicate an entry is in column C, this is where the employee ID is located.
    - For example, right now I have 1377 rows of data
    - If I add something to cell A1378 or B1378, my "Part 2" VBA would kick in and highlight the cell (color #38) pink or whatever it is. If data was entered into cell C1378, then the entire row (A1378:DD1378) would be highlighted in yellow (or orange).
    - I want the "add" script to run initially
    - Many of these cells are formulas within the other columns, it is likely that another cell within the range of AA:DD although the row was added may require the user to overwrite it.
    - In this instance, the row would be yellow (or orange) to indicate it was an added row, but then it may also contain some lpink cells to indicate cells overwritten


    Part2 - Individual cells are chnged to pink (color #38) when changed.
    - In the second line of the code, I would love it if the range is not limited
    - This process is growing substantially
    - The bi-weekly payroll has in excell of 25,000 rows
    - The range can be based on whether the row has a value in column C
    - Column C is the employee ID, which is the driving force behind all transactions


  • Hi,


    Could you post a 10 to 15 rows sample file ... ( with anonymized data ...)


    Seems to me that the whole process needs to be revisited in light of your 650'000 records a year ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Carim----


    I created a partial file... not with all the columns. I think I got to column AD. The names, addresses, employee name, pay rates... it is all bogus data, but represents the type of date that will be populated. The majority of data types that will appear on this worksheet are number values and dollar amounts, and about 75% of the worksheet is formulas. Now this worksheet is called Sheet3 (although not in the example), and the codename is also Sheet3. I would like to be able to use the codename rather than the name of the sheet, as I cannot control if the user decides to rename the sheet.

  • Carim,


    It works perfect. I adjusted the script for the

    Code
    Target.Row, 30

    which you has for my sample file to 108 columns for my actual file. It still worked perfectly. But then I realized the when people are added, I have a script that copies the formulas to all rows with data. And this works perfectly... except now that I have your awesome highlighting script, the formatting is overwritten because my "formula" script uses that "Autofill" for this. Is there a better way that this script could copy the formulas (1st row of data is always row 2, headers in row 1), and copy all the way down? I love your script and it is exactly what I need. As is this script for the formulas... I just need a way to have my UpdateFormulas script NOT overwrite the formats that your highlighting created. As I may have mentioned this template is for another department, so things like this need tt be in place. Below is my code to update all formulas when data has been added. Please forgive the crude coding, as I am still new.



    NOTE: I was just doing some additional testing on the "add" highlighting code, and I noticed something that I don't know if you can add. If we add John smith, all his data, and the formulas, the row will be orange... but if one cell has to be overwritten, it should still change to pink (that one cell), despite the row being orange. The individual cell overwrites are primarily happening when there is a formula in the cell and it is replaced by a value or another formula. We need to be able to visually see these, hence the pink color. I should have specified this. Obviously you cannot know what I am thinking, sorry about that.

  • Glad to hear you are heading in the right direction ...


    Regarding the UpdateFormulas macro... there are several possibilities ...


    You could be converting your Database into a Table


    However, why do you copy from Row 2 all the way down ?


    All the existing rows have already been updated ...


    Starting from the last used row would improve your sheet performance ...


    Regarding your way of appending New records : How is it done ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I need to keep things in basically the same format, as this template will be use by multiple other users. My company has strict protocols on things.... even silly things. In order to convert the database to a table, I need to get permission from "the powers that be". I am a lowly contractor, so I need to "color between the lines" for now.


    - Why do I copy from row 2 down for formulas? Honestly because I do not know how to code it to copy from the last row containing formulas down., That would be the best way. But since I am still new, and the amount of data varies on a regular basis, I simply had no clue. I would prefer to past the formulas from the last row of existing row of formulas. I agree, starting from the final row of formulas would be better performance, it comes down to lack of knowledge on my part.
    - Appending new records, I honestly do not know, this is not a process I am fully familiar with. I is a VERY detailed payroll process for government contracts. There are many variables, and sometimes, a formula calculation requires being overwritten in the form of a value or a revised formula. I have looked at the previous workbooks, and there are an abundance of highlighted cells and rows that had been colored manually for this purpose. It is an ongoing thing that happens constantly (and the users always select the ENTIRE row, not just the 103 actual columns within the range).


    My role is to streamline and automate the process. The end user wastes so much time formatting, inserting columns and copying and pasting formulas from the previous workbook, and the list goes on!. Highlighting changed cells and rows

  • Very honestly ... feel really sorry for you ... !!!


    It is very sad ( and almost incredible ...) to hear that you are not authorized to use Excel in a way that would allow everybody to more productive ...


    Want to wish you the Best of Luck in trying to streamline your process ... within the stupid constraints imposed on you ...:smash:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks Carim, for all your help. It is greatly appreciated. And I agree... I could not believe it either when I attempted to make some simple changes (or so I thought), and was shut down.

  • Feel free to come back to the Forum ... for new questions ... hopefully with less constraints ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Carim,
    Thanks so much! Some things require approval from our corp hq in another country... and it is just plain stupid to be honest! But I need the paycheck. And yes I shall return. Thanks again for all your help!


    ~~ Sherry

  • Carim,


    I hope you can help. There has been a revised request for this highlighting macro. The code below is what you last provided me with. What I need is that these macros are executed ONLY when the user does something MANUAL to the workbook. If the user runs a macro to import or copy data to sheets within the workbook, nothing should be highlighted. However if the user manually adds a new row, to the workbook, that row (columns A-DD) will be highlighted in orange (color index 46). I did UAT with the user today and was advised that the user will select a row that is similar and copy/paste tht row to the 1st blank row, that the user will manually change any specifics. In this instance, the entire row (A-DD) should be orange, and then as each cell is changed by the user, only those cells would be pink.
    The User copies an employee record, pastes to a new row. Then changes the employee name, emp #, start date, title, etc... only those changed cells are pink, the rest remain orange. Is this possible?


  • Sherry,


    As you very well know there are two kinds of macros :


    1. Standard macros


    2. Event macros


    The standard require the User to launch the request via the Menu, a shortcut or a button


    An event macro is triggered by an action performed by the User


    So the very first decision you should make is which route you want to take ...


    Then the second decision is related to what is exactly you do need to keep track of ... and therefore highlight ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!