CODEWITHSUNDEEP
vbaoffset
Herminne
Herminne

Reputation: 81

Range.Offset is turned into a Double

I have a strange behavior of a Range object: somehow it is turned into a Double.

here is my code:

Sub Test()

    Dim RRange As Range
    Set RRange = ThisWorkbook.Worksheets("Sheet1").Range("R4")
    Set RRange = RRange.Offset(0, -1)
    UpdateConditionalFormatting (RRange)
    UpdateConditionalFormatting (ThisWorkbook.Worksheets("Sheet1").Range("R4"))

End Sub

So the first call of UpdateConditionalFormatting will crash the program (object required), the second will work. The sub requires the Range object

I don't know what is wrong here :(

Sub UpdateConditionalFormatting(ByVal rng As Range)

    With rng.FormatConditions.AddColorScale(ColorScaleType:=3)
     .SetFirstPriority
    .ColorScaleCriteria(1).Type = xlConditionValueLowestValue
    .ColorScaleCriteria(1).FormatColor.Color = 8109667
    .ColorScaleCriteria(2).Type = xlConditionValuePercentile
    .ColorScaleCriteria(2).Value = 50
    .ColorScaleCriteria(2).FormatColor.Color = 8711167
    .ColorScaleCriteria(3).Type = xlConditionValueHighestValue
    .ColorScaleCriteria(3).FormatColor.Color = 7039480

End With
End Sub

Upvotes: 0

Views: 71

Answers (1)

jamheadart
jamheadart

Reputation: 5323

Just get rid of the brackets, use

UpdateConditionalFormatting RRange

instead of

UpdateConditionalFormatting (RRange)

Either that or use the Call syntax:

Call UpdateConditionalFormatting(RRange)

N.B: Also your 2nd call is providing a different Range because you didn't Offset it - this wouldn't cause the error you got, but I just thought I'd highlight it.

Upvotes: 3

Related Questions