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

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A2:DD2500"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Interior.ColorIndex = 38
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub

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

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.

Sub UpdateFormulas()


' Optimize Macro Speed
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual


' Sheet name & colum location to be deleted
    Sheet3.Select
    
'Range of Cells where Formulas are
' Column to designate formula must exist
    Range("I2").AutoFill Destination:=Range("I2:I" & Cells(Rows.Count, "B").End(xlUp).Row)
    Range("AC2").AutoFill Destination:=Range("AC2:AC" & Cells(Rows.Count, "B").End(xlUp).Row)
    Range("AJ2").AutoFill Destination:=Range("AJ2:AJ" & Cells(Rows.Count, "B").End(xlUp).Row)
    Range("AL2").AutoFill Destination:=Range("AL2:AL" & Cells(Rows.Count, "B").End(xlUp).Row)
    Range("AT2").AutoFill Destination:=Range("AT2:AT" & Cells(Rows.Count, "B").End(xlUp).Row)
    Range("BH2:CH2").AutoFill Destination:=Range("BH2:CH" & Cells(Rows.Count, "B").End(xlUp).Row)
    Range("CJ2:DD2").AutoFill Destination:=Range("CJ2:DD" & Cells(Rows.Count, "B").End(xlUp).Row)
    
' Sheet name & colum location to be deleted
    Sheet4.Select
    
'Range of Cells where Formulas are
' Column to designate formula must exist
    Range("I2").AutoFill Destination:=Range("I2:I" & Cells(Rows.Count, "B").End(xlUp).Row)
    Range("AC2").AutoFill Destination:=Range("AC2:AC" & Cells(Rows.Count, "B").End(xlUp).Row)
    Range("AJ2").AutoFill Destination:=Range("AJ2:AJ" & Cells(Rows.Count, "B").End(xlUp).Row)
    Range("AL2").AutoFill Destination:=Range("AL2:AL" & Cells(Rows.Count, "B").End(xlUp).Row)
    Range("AT2").AutoFill Destination:=Range("AT2:AT" & Cells(Rows.Count, "B").End(xlUp).Row)
    Range("BH2:CH2").AutoFill Destination:=Range("BH2:CH" & Cells(Rows.Count, "B").End(xlUp).Row)
    Range("CJ2:DE2").AutoFill Destination:=Range("CJ2:DE" & Cells(Rows.Count, "B").End(xlUp).Row)


' activates sheet of specific name
' Activates the "Main" tab (Sheet2)
    Sheet2.Activate


ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Sub

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.

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

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.

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?

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub
Dim i As Double
i = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
If Target.Row > i Then
   Range(Cells(Target.Row, 1), Cells(Target.Row, 108)).Interior.ColorIndex = 46
ElseIf Target.Row <= i Then
   Target.Interior.ColorIndex = 38  ' <--- Color is pink (for individual changes)
End If
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
' Mouse Right Click to Clear Colors
If Target.Row = 1 Then Exit Sub
Dim i As Double
i = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
If Target.Row > i Then
   Range(Cells(Target.Row, 1), Cells(Target.Row, 108)).Interior.ColorIndex = xlNone
ElseIf Target.Row <= i Then
   Target.Interior.ColorIndex = xlNone
End If
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Cancel = False
End Sub