Cell-Color searching function

Hi!

I'm looking for any possibility of a function that can start of from a particular cell, and move up until it hits a cell with a certain color. Then, I simply want it to output the name of that cell.

Is that possible?

jrodri14
maybe..in a kinda roundabout fashion

Function FindColour(FindRange As Variant) As String
  For e = 1 To FindRange.Cells.Count
   If FindRange.Cells(e, 1).Characters(Start:=1, Length:=1).Font.ColorIndex = 3 Then
   FindColour = FindRange.Cells(e, 1).Address
   End If
   Next
End Function

Originally Posted by pike

jrodri14
maybe..in a kinda roundabout fashion

Function FindColour(FindRange As Variant) As String
  For e = 1 To FindRange.Cells.Count
   If FindRange.Cells(e, 1).Characters(Start:=1, Length:=1).Font.ColorIndex = 3 Then
   FindColour = FindRange.Cells(e, 1).Address
   End If
   Next
End Function

I think he wants cell color, not font color.
If so change to

If FindRange.Cells(e, 1).Interior.ColorIndex = 3 Then

also three is the colour red

I think I get what you want, here's my solution.

Sub srchByColor() 'works up from whatever cell was last highlighted

Dim bottom As Integer
Dim coll As Integer

bottom = Target.Row
coll = Target.col

    For i = bottom To 1 Step -1
        If Cells(bottom, coll).Interior.ColorIndex = 3 Then
            'do whatever what you want with red cells in targeted column
            'outputting name of cell (aka address):
            msgbox cells(i,coll).address
        End If
        
        If Cells(bottom, coll).Text = "" Then Exit Sub
    Next i

End Sub

Originally Posted by jrodri14

So, I guess there is no way to ask it to start off from a particular cell, and look for the first cell above it with that particular color?

This sub will find the 1st cell above the current selection with the same cell color as the selection. Or if you know the ColorIndex that you want to locate, call it with the ColorIndex you want
Didn't test it, so let me know if it doesn't work right.
Edited to put in calling from different selection.

Sub Find_Colored_Cell(Optional xlCI as xlColorIndex = xlColorIndexNone, Optional Rng as Range)
    If Rng is Nothing then
        Set Rng = Selection
    Endif
    If xlCI = xlColorIndexNone then
        xlCI = Rng.Interior.ColorIndex
        Set Rng = Rng.Offset(-1,0)
    End If
    Do Until Rng.Interior.ColorIndex = xlCI
        If Rng.Row = 1 Then Exit Do
        Set Rng = Rng.Offset(-1,0)
    Loop
    If Rng.Interior.ColorIndex = xlColorIndex Then
        Msgbox Rng.Address
    EndIf
End Sub