Calculate button

[INDENT]Hello everyone, I'm trying to create a button (lets call it "CALCULATE") placed on sheet2 to run a code to color all the reference cells from sheet2 to sheet1, sheet3, sheet4...etc and also exclude sheet2 from the code...

Thanks!

Sub Botón1_Haga_clic_en()Private Sub Worksheet_Calculate()   'apply cells colors from single-cell formula dependencies/links   Dim Cel     As Range   Dim RefCel  As Range   On Error Resume Next   For Each Cel In ActiveSheet.UsedRange      If Cel.HasFormula Then         Set RefCel = Evaluate(Mid(Cel.Formula, 2))         Cel.Interior.Color = RefCel.Interior.Color      End If   Next CelEnd SubEnd Sub

[/INDENT]

Re: Calculate button

Quote from TheGlovner;791084

You're not far off, two ways you could tackle things, I'm more inclined to have a button be used to call a larger section of code rather than embedding the code within the button click event so I'll show you that:

Code

Sub Botón1_Haga_clic_en() 
    Call Worksheet_Calculate
End Sub


'apply cells colors from single-cell formula dependencies/links
Private Sub Worksheet_Calculate() 


Dim Cel       As Range 
Dim RefCel  As Range 


On Error Resume Next 


     For Each Cel In ActiveSheet.UsedRange 
         If Cel.HasFormula Then 
             Set RefCel = Evaluate(Mid(Cel.Formula, 2)) 
             Cel.Interior.Color = RefCel.Interior.Color 
         End If 
     Next Cel
End Sub

Display More

Your issue was around the fact you were trying to embed the functional subroutine inside the button click event routine, rather than calling it from the button click routine.

Thanks for the tip!, but the idea is: for example, when i colored a couple of cells on "sheet2" with red, and those cells are linked on sheet3, sheet1, etc, etc, in white, when i click the button, all the sheets takes reference from the cell color of sheet2 to fill it with red color those linked cells on sheet 3, sheet1, sheet4, etc etc