Finding last Green (5287936) Filled Cell in Row

endrese

New Member
Joined
Mar 25, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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,
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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).
 
Upvote 0
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).
 
Upvote 0
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?
 
Upvote 0
First please answer my prior question: What is the value of col when the error occurs?
 
Upvote 0
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.

VBA Code:
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

1648564646862.png


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.
 
Upvote 0
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:

Rich (BB code):
    '~~> 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)
 
Upvote 0
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
 

Attachments

  • code_mod.PNG
    code_mod.PNG
    1.5 KB · Views: 5
Upvote 0
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
Rich (BB code):
If col > 0 Then
   s.Activate
   s.Cells(row, col).Select ' *** Debug stopped here
End If
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top