Locate any green or yellow cell in a range, return offset

[davedave24]

Hello

I'm having trouble with this:

I need to locate any green (5296274) or yellow (&HFFFF&) cell within a range (A1:AZ300) on a specific worksheet ("DATES")

I then need to return the offset (col +1) for each (likely loading them into a standard listbox).

I've been trying to adapt this -

http://www.thespreadsheetguru.com/the-code-vault/2014/4/21/find-all-instances-with-vba

- with little success.

Any help appreciated on this

Cheers all

 

[SkipVought]

Hi,

How did these cells get their color?

Is this a Conditional Format, which is the feature that Excel employs to best apply formates to various cells? If this is the case, then use the logic that is applied in the CF. Otherwise it will be a bit more obtuse.

 

[SkipVought]

If the user has applied a greenish or yellowish color, who's to know what ACTUAL color has been applied to any cell??? If that format code is not EXACTLY what you expect the used to have applied, then it is not the color specified!!!

That's why Conditional Formatting is the way to apply formats conditionally, under controlled pre-specified conditions of your choice--NOT someone else's chioce!

 

[Andrzejek]

Unless there are only Green and Yellow cells among all other (White) cells. So the logic may eventually be:
If the cell’s color is <> White Then do whatever ...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Please post what code you have tried and explain the results that fail to meet your expectations/requirements.

 

[davedave24]

Hi there, I actually got this to work in the end. Skip - the cells are coloured by conditional format and are always those 2 specific colours.

 'ThisWorkbook.Sheets("CERT DATES").Select
    Set rRng = ThisWorkbook.Sheets("CERT DATES").Range("A1:A200,E1:E200,I1:I200,M1:M200")
    
    On Error Resume Next

    For Each rCell In rRng.Cells
        If rCell.Interior.Color = 5296274 And rCell.Offset(0, 2).Value <= (Date + lDays) Then  'yellow
            
            With Sheets("ON Hire List").Range("C:T")
                Set rngFindSerial = .Find(rCell.Value, , xlValues, MatchCase:=False)
            End With
                    
            listAvailable.List(listAvailable.ListCount - 1, 1) = rCell.Value
            listAvailable.List(listAvailable.ListCount - 1, 2) = Format(rCell.Offset(0, 2).Value, "DD-MM-YY")
            listAvailable.List(listAvailable.ListCount - 1, 3) = rCell.Offset(0, 2).Value - Date
                      
        End If
    Next rCell

 

[SkipVought]

Conditional Format is being used.

Then why would you look for colors???

Use the CF LOGIC! No brainier!