tonyrosen
12-21-2005, 02:06 PM
I've created the following code:
Sub ValidateRetirements()
Dim rRange As Range
Dim rRange1 As Range
Dim wSheet As Worksheet
Dim wSheet1 As Worksheet
Dim x As Integer
Set wSheet = Worksheets("Retirements")
Set wSheet1 = Worksheets("ConsolidationSheet")
For x = 1 To 5
Select Case x
Case 1
Set rRange = Range("Retirements_AV_Land")
Case 2
Set rRange = Range("Retirements_AV_Mineral")
Case 3
Set rRange = Range("Retirements_AV_Buildings")
Case 4
Set rRange = Range("Retirements_AV_Machines")
Case 5
Set rRange = Range("Retirements_AV_Rentals")
End Select
Set rRange1 = Range("LessSale" & x)
If rRange <> rRange1 Then
' Change the background color to yellow
rRange.Interior.ColorIndex = 6
rRange1.Interior.ColorIndex = 6
' Insert a comment with the error
With rRange
If rRange.Comment Is Nothing Then
.AddComment
.Comment.Text Text:="Amount entered does not equal the amount on the ConsolidationSheet column *Less Sales / Retirements At Cost*"
End If
End With
With wrRange1
If rRange1.Comment Is Nothing Then
.AddComment
.Comment.Text Text:="Amount entered does not equal the amount on the Retirements sheet - column *Acq Value*"
End If
End With
Else
' Change the background color back to the original color
rRange.Interior.ColorIndex = 35
rRange1.Interior.ColorIndex = 0
' Delete the comment
With rRange
.ClearComments
End With
With rRange1
.ClearComments
End With
End If
Next x
End Sub
However, "rRange" and "rRange1" keep throwing me an error. Is there another way around this?
Sub ValidateRetirements()
Dim rRange As Range
Dim rRange1 As Range
Dim wSheet As Worksheet
Dim wSheet1 As Worksheet
Dim x As Integer
Set wSheet = Worksheets("Retirements")
Set wSheet1 = Worksheets("ConsolidationSheet")
For x = 1 To 5
Select Case x
Case 1
Set rRange = Range("Retirements_AV_Land")
Case 2
Set rRange = Range("Retirements_AV_Mineral")
Case 3
Set rRange = Range("Retirements_AV_Buildings")
Case 4
Set rRange = Range("Retirements_AV_Machines")
Case 5
Set rRange = Range("Retirements_AV_Rentals")
End Select
Set rRange1 = Range("LessSale" & x)
If rRange <> rRange1 Then
' Change the background color to yellow
rRange.Interior.ColorIndex = 6
rRange1.Interior.ColorIndex = 6
' Insert a comment with the error
With rRange
If rRange.Comment Is Nothing Then
.AddComment
.Comment.Text Text:="Amount entered does not equal the amount on the ConsolidationSheet column *Less Sales / Retirements At Cost*"
End If
End With
With wrRange1
If rRange1.Comment Is Nothing Then
.AddComment
.Comment.Text Text:="Amount entered does not equal the amount on the Retirements sheet - column *Acq Value*"
End If
End With
Else
' Change the background color back to the original color
rRange.Interior.ColorIndex = 35
rRange1.Interior.ColorIndex = 0
' Delete the comment
With rRange
.ClearComments
End With
With rRange1
.ClearComments
End With
End If
Next x
End Sub
However, "rRange" and "rRange1" keep throwing me an error. Is there another way around this?