Clairexcel
New Member
- Joined
- Mar 23, 2021
- Messages
- 31
- Office Version
- 2016
- 2010
Hello everyone, I am still working on my excel file, which is now almost finished.
I realized though, I do not want all the formulas to be in the cells, as the users may by mistake cancel the formulas or modify them. So I thought of a VBA change event.
I wanted to write the code all by myself, so I found achange vent code that I tweakd with the code I got by registering macros. I was so happy untill I relaized I wrote a mess
I am still new to VBA and while macros I can teak and begn to understand them better, chnge events are something very new to me.
Anyhow. what I tried to do is.
In my file, the user insert "si" in cell A of a row if he/she is inserting a new project. In this case is he writes "si" in cell A, then cells from A to T in that row should become light blue. And also if the cell A contains the word "si" the code should also perform calculations in Cells K, Q, P and T on the same row., and the user mut not be able to fill these cells manually.
Example : if A2 contains "si" then In cell K2 vba should automatically perform the sum of all the cells K bellow it (from K 3 to K5000) but only if value in the cells G bellow (G3:G5000) it is equal to the value in the cell AI. Same calculation for cell Q .
But! if Cell A of the row contains "no" than cells from C to H must copy automatically the values from C to H above them. And , in this case the cells K, Q must allow the user to insert values manually without calculations
I realize that my code as it is is a mess,. Any help will be highly appreciated, I am eager to learn. Thank you
I realized though, I do not want all the formulas to be in the cells, as the users may by mistake cancel the formulas or modify them. So I thought of a VBA change event.
I wanted to write the code all by myself, so I found achange vent code that I tweakd with the code I got by registering macros. I was so happy untill I relaized I wrote a mess
I am still new to VBA and while macros I can teak and begn to understand them better, chnge events are something very new to me. Anyhow. what I tried to do is.
In my file, the user insert "si" in cell A of a row if he/she is inserting a new project. In this case is he writes "si" in cell A, then cells from A to T in that row should become light blue. And also if the cell A contains the word "si" the code should also perform calculations in Cells K, Q, P and T on the same row., and the user mut not be able to fill these cells manually.
Example : if A2 contains "si" then In cell K2 vba should automatically perform the sum of all the cells K bellow it (from K 3 to K5000) but only if value in the cells G bellow (G3:G5000) it is equal to the value in the cell AI. Same calculation for cell Q .
But! if Cell A of the row contains "no" than cells from C to H must copy automatically the values from C to H above them. And , in this case the cells K, Q must allow the user to insert values manually without calculations
I realize that my code as it is is a mess,. Any help will be highly appreciated, I am eager to learn. Thank you
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A")) Is Nothing Then
On Error GoTo M
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Row > 1 Then
Dim r As Long
'Dim ans As Long
ans = Target.Value
r = Target.Row
'If Cell A contains tex "si" then cells from A:T must become blue
If Cells(r, "A").Value = "si" Then Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A2=""si"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.599963377788629
'and then, also if A contains "si also calulate the following formulas in cell K , cell Q and Cell T as below
Cells(r, "K").Formula = "=SUMIFS(R3C11:R5000C,R3C1:R5000C1,""<>si"",R3C7:R5000C7,R[1]C[24])"
Cells(r, "Q").Formula = "=SUMIFS(R2C17:R5000C17,R2C1:R5000C1,""<>si"",R2C7:R5000C7,RC[18])"
Cells(r, "T").Formula = "=RC16-RC17"
Else If
'But If cell A contains the text "no" then, in cells from C to H copy the values of the cells C:H row above.
If Cells(r, "A").Value = "no" Then
Range("C5").Select
ActiveCell.FormulaR1C1 = "=IF(RC1=""NO"",R[-1]C,"""")"
Range("D5").Select
End If
Exit Sub
M:
MsgBox "You entered " & ans & " into cell which is a improper value"
End Sub
