Worksheet_Calculate event for multilple cells

DANDERSON · Oct 11th 2004

I am including a sample file that can illustrate what I have working so far.

I basically want to watch multiple cells, and if their values change,(by comparing them to the Check sheet), code will execute.

I want to watch cells on (sheet Working Data, rows 24 & 25, cells B-BJ.) and compare them to (sheet Check, rows 24 & 25, cells B-BJ).

It works great for the sigle test cell B2, but I now want to watch multiple cells.

File Attached:

Thanks for any help, sugesstions.
Dan

Sheet2 Working Data:

Code

Private Sub Worksheet_Calculate()
   Worksheet_Change Range("B24")
End Sub


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim checkStr As String


checkStr = Sheets("Check").Range("B24")
'   Level 1:  Set up the event to watch a single cell.
    If Target.Address = Range("B24").Address Then


'       Level 2: Perform some action based on the value of the watched cell.
        Select Case Target.Value
            Case Is <> checkStr
                Target.Font.ColorIndex = 2
                    With Target.Interior
                        .ColorIndex = 3
                        .Pattern = xlSolid
                    End With
                Range("A3:A6").Select
                Selection.Font.ColorIndex = 2
                    With Selection.Interior
                        .ColorIndex = 3
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        Range("B24").Select
                    End With
            Case Is = checkStr
            Target.Font.ColorIndex = 5
                With Target.Interior
                        .ColorIndex = xlColorIndexNone
                        .Pattern = xlPatternNone
                    End With
        End Select
    End If
End Sub




Module1:
Sub GoalSeekQuick2()


                    Sheets("working data").Select
                    Range("A3:A6").Select
                    Selection.Font.ColorIndex = 10
                    With Selection.Interior
                        .ColorIndex = 10
                        .Pattern = xlSolid
                    End With
                    Rows("24:25").Select
                    Selection.Interior.ColorIndex = xlNone
                    Selection.Font.ColorIndex = 5
                    Selection.Copy
                    Sheets("Check").Select
                    Range("A24").Select
                    ActiveSheet.Paste
                    Range("A1").Select
                    Sheets("working data").Select
                    ActiveWindow.SmallScroll Down:=-1
                    Range("A1").Select
                    Application.CutCopyMode = False
                    Range("B24").Select
                
                    MsgBox "test macro executed"
End Sub

Display More

Richie(UK) · Oct 11th 2004

Hi Dan,

I'm not able to download files at the moment, but in general terms you would check for a change in a range of cells by using Intersect - like this:[vba]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngtest As Range

Set rngtest = Range("B24:BJ25")
If Not Application.Intersect(Target, rngtest) Is Nothing Then
MsgBox "Change in our range!"
End If

End Sub[/vba]HTH

DANDERSON · Oct 11th 2004

Thanks for the Help Richie,
How would they be compared to the cells for verification on the Check sheet as shown in the Cases?

All the code is posted, that is in the file, just enter values of your own to test on rows given.

Thanks, perplexed....

Richie(UK) · Oct 11th 2004

Hi Dan,

Is this what you had in mind?[vba]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngtest As Range

Set rngtest = Range("B24:BJ25")
If Not Application.Intersect(Target, rngtest) Is Nothing Then
With Target
If .Value = Worksheets("Check").Range(.Address) Then
.Font.ColorIndex = 5
.Interior.ColorIndex = xlColorIndexNone
.Interior.Pattern = xlPatternNone
Else
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
.Interior.Pattern = xlSolid
Range("A3:A6").Font.ColorIndex = 2
With Range("A3:A6").Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End With
End If

End Sub[/vba]HTH

DANDERSON · Oct 12th 2004

Awesome! That's it!

Thank you very much. That's exactly the outcome I was looking for. I thought I was close, but I like your solution better, and I learned something new.
Thank you very much Richie(UK).

Worksheet_Calculate event for multilple cells

Participate now!

Share