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 tried your code and I did not get an error, putting a green cell in K1. What is the value of col when the error occurs? That error suggests that the arguments for Cells are not valid (not much else that can go wrong there). We know row has to be 1, so col is the question.

Another thing but this is not causing your error: s.Columns.Count will be 16,484 regardless of what cells you are actually using. You can use s.UsedRange.Columns.Count if the cell with green contains data (it may not work if the green cells are blank).

 

[endrese]

I tried your code and I did not get an error, putting a green cell in K1. What is the value of col when the error occurs? That error suggests that the arguments for Cells are not valid (not much else that can go wrong there). We know row has to be 1, so col is the question.

Another thing but this is not causing your error: s.Columns.Count will be 16,484 regardless of what cells you are actually using. You can use s.UsedRange.Columns.Count if the cell with green contains data (it may not work if the green cells are blank).

 

[endrese]

The cells don't contain data as in numbers/text but this sheet does pull from a database to track progress, thus the reason for finding the last green cell in the column.
Would these linked cells be an issue in completing the above operation?

 

[6StringJazzer]

First please answer my prior question: What is the value of col when the error occurs?

 

[Siddharth Rout]

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,

You can use .Find with .FindFormat.Interior.Color to search for a cell which has the color. Here is an example.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
 
    '~~> This returns the cell address which has the color
    MsgBox GetCellAddress(ws, 1, 5287936)
End Sub

'~~> Function to find the cell which has the color
Private Function GetCellAddress(ByVal ws As Worksheet, ByVal rw As Long, ByVal myColor As Long) As String
    Dim rng As Range
 
    On Error GoTo Whoa
 
    '~~> Set your color
    Application.FindFormat.Interior.Color = myColor
 
    '~~> Attempt to find the cell with the color
    Set rng = ws.Rows(rw).Find(What:="", _
                               LookIn:=xlFormulas2, _
                               LookAt:=xlPart, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, _
                               MatchCase:=False, _
                               SearchFormat:=True)
   
    '~~> Return the result
    If Not rng Is Nothing Then GetCellAddress = rng.Address Else GetCellAddress = "Not Found"
 
LetsContinue:
    Application.FindFormat.Clear
 
    Exit Function
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Function

Note: If there are more than one cell which can have green color then you can use .FindNext to get the last cell which has the color in that row.

 

[6StringJazzer]

Using Find to match a format is great idea and I did not realize that was possible.

One change: To find the last cell in a row with that format, search backwards:

    '~~> Attempt to find the cell with the color
    Set rng = ws.Rows(rw).Find(What:="", _
                               After:=ws.Rows(rw).Cells(1), _
                               LookIn:=xlFormulas2, _
                               LookAt:=xlPart, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlPrevious, _
                               MatchCase:=False, _
                               SearchFormat:=True)

 

[endrese]

First please answer my prior question: What is the value of col when the error occurs?

The value of Col= 0.
Again, I'm not too familiar with VBA so I figured I could store the variable as col=col+6 but the error still persists. Triggered at the same line as above:
s.Cells(row, col).Select ' *** Debug stopped here

 

[6StringJazzer]

0 is not a valid column index. It must be a positive integer. This means that your For loop did not find any green cells in that row.

To fix your code make this change

If col > 0 Then
   s.Activate
   s.Cells(row, col).Select ' *** Debug stopped here
End If

 

[endrese]

The cells have conditional formatting applied.
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) only via cell value.
The conditional formatting rules I have are for a yellow cell (cell value = 2) whereas for a green cell (cell value =1).