PDA

View Full Version : Solved: "Dynamic" Range (for lack of a better term) not 'registering'



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?

tonyrosen
12-21-2005, 02:19 PM
Disregard ... I still had the sheet protected :|

Zack Barresse
12-21-2005, 02:19 PM
Great! A question and solution without any other posts! Thanks for posting!