CODEWITHSUNDEEP
excelvba
user3552634
user3552634

Reputation: 1

vba worksheet_change for those cells in a different sheet

I have developed a macro that checks a value in a range, and depending the value that was before the change it applies bold to some values in a different columns from the same row. that is working ok And I 'm happy abbout get that. the portion of the code is this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim KeyCells As Range

   Dim valorActual As Long

   Dim valorAntic As Long

Set KeyCells = Range("BF3:BF378")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then



   valorActual = Target.Value
 valorAntic = Rows(Target.Row).Columns("BG").Value


     If (valorActual > 4 And valorAntic < 5) Or (valorActual > 8 And valorAntic < 9) Or (valorActual > 12 And valorAntic < 13) Or (valorAntic > 4 And valorActual < 5) Or (valorAntic > 8 And valorActual < 9) Or (valorAntic > 12 And valorActual < 13) Then

     Rows(Target.Row).Columns("C").Font.Bold = True
     Rows(Target.Row).Columns("C").Font.Underline = True
     Rows(Target.Row).Columns("R").Font.Bold = True
     Rows(Target.Row).Columns("R").Font.Underline = True
     End If

The problem is that now that this works as it should, I want to check this from another sheet. in other words, until now the only thing i was checking is column bf. and if a new value was there i check it. but in the deployment moment i realize that this column is changed because another in other sheet had been changed before. What I mean is that the real change is in another sheet and the column bf what has inside them cells is "= anotherSheet!thecellchanged" and if this "another sheet" changes, the macro from the original worksheet change it is not called,

so is there a way to check the changes from a sheet that affects 3rd sheets, and taking the range of the ="anothersheet" cells? thanks for your website and help in advance

markus http://www.funhelps.com

Upvotes: 0

Views: 2738

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Your code is in the ThisWorkbook code module, so any references you make to ranges in your macro will refer to the ActiveSheet by default, unless you explicitly qualify them with a sheet reference.

In this case that reference is the Sh parameter passed to your event handler: you should use that in your code to make sure you're dealing with the correct worksheet.

E.g. :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Dim KeyCells As Range
   Dim valorActual As Long
   Dim valorAntic As Long

   Set KeyCells = Sh.Range("BF3:BF378")

   If Not Application.Intersect(KeyCells, Target) Is Nothing Then

       valorActual = Target.Value
       valorAntic = Target.EntireRow.Cells(1,"BG").Value


     If (valorActual > 4 And valorAntic < 5) Or (valorActual > 8 And _
         valorAntic < 9) Or (valorActual > 12 And valorAntic < 13) Or _
         (valorAntic > 4 And valorActual < 5) Or (valorAntic > 8 And _
         valorActual < 9) Or (valorAntic > 12 And valorActual < 13) Then

         With Sh.Rows(Target.Row)
             .Columns("C").Font.Bold = True
             .Columns("C").Font.Underline = True
             .Columns("R").Font.Bold = True
             .Columns("R").Font.Underline = True
         End With
     End If

Upvotes: 0

Related Questions