A Macro is called when value in column changes in Excel. Every time the value change in a column, the macro will run. The macro is triggered by a change In column values.
Check out my online courses
For more help visit my website or email me at [email protected].
Contact me regarding customizing this template for your needs.
Excel one-on-one on-line training available. Email me to arrange.
I am able to provide online help on your computer at a reasonable rate.
Check out my next one-hour Excel Webinar
I use a Blue condensor Microphone to record my videos, here is the link
Check out Crowdcast for creating your webinars
If you need to buy Office 2019 follow
I use Tube Buddy to help promote my videos
Check them out
Follow me on Facebook
TWEET THIS VIDEO
Follow me on twitter
easyexcelanswers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I’ve used or have experience with.
code
Sub changestatnumber()
Dim stat As Range
Dim r As Integer
r = 7
Set stat = Sheet1.Range(“A7:A71”)
For Each cell In stat
If stat.Cells(r, 1).Value (symbol for not equal) stat.Cells(r – 1, 1).Value Then
Call enterhrs(r)
End If
r = r + 1
Next
End Sub
Sub enterhrs(r)
Dim x As Integer
Sheet1.Cells(r, 4).Select
‘shift one
x = 1
For x = 1 To 1
ActiveCell.Value = “N”
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 13)).Value = “N”
Range(ActiveCell.Offset(0, 14), ActiveCell.Offset(0, 20)).Value = “OFF”
Range(ActiveCell.Offset(0, 21), ActiveCell.Offset(0, 34)).Value = “D”
Range(ActiveCell.Offset(0, 35), ActiveCell.Offset(0, 41)).Value = “OFF”
ActiveCell.Offset(0, 42).Select
Next x
Sheet1.Cells(r + 1, 4).Select
x = 1
For x = 1 To 1
ActiveCell.Value = “D”
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 6)).Value = “D”
Range(ActiveCell.Offset(0, 7), ActiveCell.Offset(0, 13)).Value = “OFF”
Range(ActiveCell.Offset(0, 14), ActiveCell.Offset(0, 27)).Value = “N”
Range(ActiveCell.Offset(0, 28), ActiveCell.Offset(0, 34)).Value = “OFF”
Range(ActiveCell.Offset(0, 35), ActiveCell.Offset(0, 41)).Value = “D”
ActiveCell.Offset(0, 42).Select
Next x
Sheet1.Cells(r + 2, 4).Select
x = 1
For x = 1 To 1
ActiveCell.Value = “OFF”
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 6)).Value = “OFF”
Range(ActiveCell.Offset(0, 7), ActiveCell.Offset(0, 20)).Value = “D”
Range(ActiveCell.Offset(0, 21), ActiveCell.Offset(0, 27)).Value = “OFF”
Range(ActiveCell.Offset(0, 28), ActiveCell.Offset(0, 41)).Value = “N”
ActiveCell.Offset(0, 42).Select
Next x
End Sub
