CODEWITHSUNDEEP
excelvba
Kay Singian
Kay Singian

Reputation: 1391

Excel VBA Function For Each Cell in Selection error resulting to Variable Not Defined

I was writing A code function in excel that calls a subroutine after the function gets the input data. The function is to color a cell or range of cells within a worksheet or another worksheet using the RGB values in the excel.

However, I do not know how to call the cell values with the function I wrote once the subroutine is working. The Error says Variable not Defined. Looks like the "For Each Cell in Selection" line is causing the error, as the cell is not declared once I passed the data of the function to the subroutine. Also, can someone provide the code on modifying other worksheet's cells in the form of: "Cell.Interior.Color = RGB(Red, Green, Blue)"? The worksheet's name is provided to be input by the user. 

Option Explicit
Dim Activate As Boolean

Public Function SETRGBCOLOR( _
    Optional CurrentCellValue As Variant, _
    Optional wksSheetname As String, _
    Optional rngRange As Range, _
    Optional byteRed As Byte = 0, _
    Optional byteGreen As Byte = 0, _
    Optional byteBlue As Byte = 0) As Variant

    If IsMissing(CurrentCellValue) Then
        SETRGBCOLOR = ""
    Else
        SETRGBCOLOR = CurrentCellValue
    End If

    Activate = False

    'set greater RGB values than max RG value to 255, byte ignores the negative value of a digit
    If byteRed > 255 Then byteRed = 255
    If byteGreen > 255 Then byteGreen = 255
    If byteBlue > 255 Then byteBlue = 255

    Activate = True

    Call CalculateColor(wksSheetname, rngRange, byteRed, byteGreen, byteBlue)
End Function

Private Sub CalculateColor(wksSheetname As String, rngRange As Range, byteRed As Byte, byteGreen As Byte, byteBlue As Byte)
    If Activate = True Then

        If Trim(wksSheetname) <> "" Then
            If Trim(rngRange) <> "" Then
                For Each Cell In Selection
                    Cell.Interior.Color = RGB(byteRed, byteGreen, byteBlue)
                Next Cell
            Else
            End If
        Else
            If Trim(rngRange) <> "" Then
                'For Each Cell In Selection
                '    Cell.Interior.Color = RGB(byteRed, byteGreen, byteBlue)
                'Next Cell
            Else
            End If
        End If
    End If
    Activate = False
End Sub

Upvotes: 1

Views: 8927

Answers (1)

grahamj42
grahamj42

Reputation: 2762

You have Option Explicit so the variable Cell must be declared : Dim Cell As Range at the start of your Sub CalculateColor().

Upvotes: 5

Related Questions