-
Solved: "Dynamic" Range (for lack of a better term) not 'registering'
I've created the following code:
[VBA]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[/VBA]
However, "rRange" and "rRange1" keep throwing me an error. Is there another way around this?
-
Disregard ... I still had the sheet protected :|
-
Great! A question and solution without any other posts! Thanks for posting!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules