A Macro is called when value in column changes in Excel

Barb Henderson
Barb Henderson
2.5 هزار بار بازدید - 5 سال پیش - A Macro is called when
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.
For more help visit my website http://www.easyexcelanswers.com  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.
I use a Blue condenser Microphone to record my videos, here is the link
https://amzn.to/37gyyGa

Check out Crowdcast for creating your webinars
https://app.linkmink.com/a/crowdcast/83

If you need to buy Office 2019 follow
https://amzn.to/2VX5dv8

I use Tube Buddy to help promote my videos
Check them out
https://www.Tubebuddy.com/easyexcelan...

Follow me on Facebook
Facebook: easyexcel.answers

TWEET THIS VIDEO A Macro is called when value in colum...

Follow me on twitter
easyexcelanswers

IG @barbhendersonconsulting

You can help and generate a translation to you own language
http://www.youtube.com/timedtext_cs_p...

*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
5 سال پیش در تاریخ 1398/11/13 منتشر شده است.
2,541 بـار بازدید شده
... بیشتر