Getting Value of a coloured cell(vba)

[excel.vba]

Hello

I am currently working on a project that requires me to send over the value of a coloured cell from one sheet to another. The code needs to search the range for that particular colour and send over the value in that coloured cell

Here is my code on that:
Public Function ColourValue(rnArea As Range, ColIndex As Long) As Range
Dim rnCell As Range, rRange As Range
For Each rnCell In rnArea
If rnCell.Interior.ColorIndex = ColIndex Then
ColourCOL = rnCell
End If
Next rnCell
End Function

Would gladly appreciate any help. Thank you

 

[Sektor]

And what is the question, anyway? What is variable "ColourCOL"?? Where it's used?
I guess this:

Public Function ColourValue(rnArea As Range, ColIndex As Long) As Range
    Dim cell As Range, rngUnion As Range
    For Each cell In rnArea
        If rnCell.Interior.ColorIndex = ColIndex Then
            [COLOR="Red"][COLOR="#ff0000"]Set rngUnion[/COLOR] = Union(rngUnion, cell)[/COLOR]
        End If
    Next rnCell
    [COLOR="Red"]Set ColourValue = rngUnion[/COLOR]
End Function

 

[excel.vba]

Oops sorry! Forgot to change that. The code is not working.It returns #Value when i use this function.

Public Function ColourValue(rnArea As Range, ColIndex As Long) As Range
Dim rnCell As Range, rRange As Range
For Each rnCell In rnArea
If rnCell.Interior.ColorIndex = ColIndex Then
ColourValue = rnCell
End If
Next rnCell
End Function

 

[excel.vba]

Thank you So much! Didnt realise that i forgot to add the SET there..
It works!

 

[Sektor]

See my previous post.

 

[mikerickson]

Here's a different version of the same approach.

Function CellsHoldingColor(rangeToSearch As Range, colorIndexSought As Long) As Range
    Dim oneCell As Range
    On Error GoTo ErrorRoutine
    
    For Each oneCell In rangeToSearch
        With oneCell
            If .Interior.ColorIndex = colorIndexSought Then
                Set CellsHoldingColor = Application.Union(CellsHoldingColor, .Cells)
            End If
        End With
    Next oneCell
    
ErrorRoutine:
    If Err = 5 Then
        Set CellsHoldingColor = oneCell
        Resume
    End If
    On Error GoTo 0
End Function

Sub test()
    Dim coloredCells As Range
    
    Set coloredCells = CellsHoldingColor(Range("A1:a10"), 6)
    
    If coloredCells Is Nothing Then
        MsgBox "no yellow cells"
    Else
        MsgBox coloredCells.Address & " are yellow."
    End If
End Sub

Odd observation:

As written, test returned "$A$2,$A$4,$A$8 are yellow."

But if the order of the arguments of Union were switched

Set CellsHoldingColor = Application.Union(.Cells, CellsHoldingColor)

Then "$A$8:$A$9,$A$4,$A$2 are yellow." was returned. The Areas of the range were in different order.