Hi
Try
Code:
Sub Check_Variance()
Dim sh As Worksheet, cCell As Range
Dim cAddress As String, lastRowSH1 As Long, i As Long
Dim startCell As Range, endCell As Range, myRange As Range
For Each sh In Worksheets
If UCase(sh.Name) <> "CONSOLIDATED" Then
cAddress = ""
With sh
lastRowSH1 = .Range("A" & .Rows.Count).End(xlUp).Row
For Each cCell In .Range("A1:A" & lastRowSH1)
Debug.Print sh.Name
Debug.Print cCell.Value
If UCase(cCell.Value) = "CHECK" Then
Set startCell = .Range("B" & cCell.Row)
Set endCell = .Range("Q" & cCell.Row)
Set myRange = .Range(startCell, endCell)
If Application.WorksheetFunction.Sum(myRange) <> 0 Then
For i = 1 To 26
If cCell.Offset(0, i) <> 0 Then
If cAddress = "" Then
cAddress = cCell.Offset(0, i).Address
Else
cAddress = cAddress & ", " & cCell.Offset(0, i).Address
End If
End If
Next i
End If
End If
Next cCell
If cAddress <> "" Then
MsgBox "Please check the following address(es) on " & sh.Name & vbNewLine & vbNewLine & cAddress
End If
End With
End If
Next sh
End Sub
Bookmarks