CODEWITHSUNDEEP
vbaexcel
user3086751
user3086751

Reputation: 205

VBA finds cell range error

I am putting in the range as follows MyRange=f2:g10, I would like how i would able to go through f1,f2 ect. where the question mark is. how am i able to do this, 

Function CountRed(MyRange As Range)
CountRed = 0

Range(?).Interior.Color = RGB(255, 0, 0)
End Function

Upvotes: 0

Views: 57

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

I would use a sub rather than a UDF:

Sub MAIN()
    Dim MyRange As Range
    Set MyRange = Range("F2:G10")
    Call CountRed(MyRange)
End Sub

Sub CountRed(r As Range)
    r.Interior.Color = RGB(255, 0, 0)
End Sub

EDIT#1:

I would use a sub rather than a UDF because:

  • no value is being returned
  • worksheets cells are being modified

For more tutorial info see OZGRID UDF Training

Upvotes: 1

Andy G
Andy G

Reputation: 19367

You can set the colour all in one go:

MyRange.Interior.Color = RGB(255, 0, 0)

or create a Range variable and loop through each cell:

Dim rng as Range

For Each rng In MyRange
    rng.Interior.Color = RGB(255, 0, 0)
Next rng

Upvotes: 2

Related Questions