Finding last Green (5287936) Filled Cell in Row

[endrese]

Happy Friday, I am attempting to find the last green (color Id=5287936) filled cell for each row in a data set. Once found I need to report the appropriate column label - (text present in row 4), for each identified green filled cell. I know I would need a for loop in order to loop through the whole dataset (multiple rows), but thought I'd start with just one row. My code is as follows:
_____________________________________________________________________________________________________
Sub findgreen()

ActiveWorkbook.RefreshAll 'Refreshes/Pulls data

Dim s As Worksheet
Set s = ActiveSheet ' look on the active sheet
'Set s = ThisWorkbook.Worksheets("Sheet1") ' look on a specific sheet

Dim color As Long
color = 5287936 ' enter the result of the statement above here

Dim row As Integer
row = 1

Dim col As Integer

For col = s.Columns.Count To 1 Step -1
If s.Cells(row, col).Interior.color = color Then Exit For
Next

s.Activate
s.Cells(row, col).Select ' *** Debug stopped here


End Sub

___________________________________________________________________________________________________________________


However, I am receiving an error: application-defined or object-defined error at the " s.Cells(row,col).Select" line

Again, I am new to this. Any input in terms of code adjustment or resources would be greatly appreciated.

Thank you in advance,

 

[6StringJazzer]

I've been reading that there is no way to write a macro determining the last cell in a row with specified color (if conditionally formatted)

Whatever you read is wrong. However, it cannot be done using the Find method. We have to return to your original approach, modifying one line of code as shown:

If s.Cells(row, col).DisplayFormat.Interior.color = color Then Exit For

Also re-read post #9 if you are going to this, which explains why you were getting the error.

 

[endrese]

Thank you for your patience.
I tested the initial code (#1) on an blank workbook with a random cell filled green on the first row and it selected the green cell no problem.
However, with or without the modification listed above (shown below) I am still receiving the application defined error when I input the same code on the desired workbook.
I really appreciate the help I've been receiving and I don't understand how the book I was reading was wrong.
Is there another reference/resource I could utilize to help troubleshoot this problem?


Code is as follows:

Sub mrexcl()

ActiveWorkbook.RefreshAll 'Refreshes/Pulls data

Dim s As Worksheet
Set s = ActiveSheet ' look on the active sheet
'Set s = ThisWorkbook.Worksheets("Sheet1") ' look on a specific sheet

Dim color As Long
color = 5287936 ' enter the result of the statement above here

Dim row As Integer
row = 1

Dim col As Integer

For col = s.Columns.Count To 1 Step -1
If s.Cells(row, col).DisplayFormat.Interior.color = color Then Exit For
Next

s.Activate
s.Cells(row, col).Select ' *** Debug stopped here





End Sub





Thank you in Advance,

Eileen Endres

 

[6StringJazzer]

Your code works perfectly for me when there is a green cell. When there is not a green cell, I get an error on the same line you pointed out. Therefore I assume that you do not have a green cell. Make sure your green cell has a color number of exactly 5287936. The RGB number is (0, 176, 80).

 

[endrese]

I can confirm that the color in the current green cells is the standard green, 5287936. The conditional formatting rule that changes cells to this green color, following the refreshing of data is seen in the image.
Could the application defined error be a result of the conditional formatting rule?

 

[6StringJazzer]

I don't know why it doesn't work in your file. All I can say is that it works for me, using conditional formatting. Unfortunately the picture doesn't help. I have attached my test file so you can compare it to yours.

In my file the cell turns green if the value is 12. When you click the button, it will find the last green cell on the row that has the currently selected cell. (Note that your code always looks in row 1.)

endrese=find green.xlsm

Shared with Dropbox

www.dropbox.com

 

[endrese]

When I run the code I receive error 400. Any suggestions?