Count number of rows in Excel

[rschneeg]

New to VB macros.
Need excel 2003 code to count the number of rows . I need to find last row and process all the rows in between. Beginning column/row is the same but the last row could change based on inserts.
The values in the cells are background colors. If any are red I need to set an overall indicator in a different cell to red. Same for yellow and green.

 

[PHV]

At least 2 FAQs here cover your issue:

http://www.tek-tips.com/faq.cfm?pid=707

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rschneeg]

I've seen that macro but what I need to do is 1st determine what the last row that has the background colors in it (there's other rows after that) and then use that as the ending row in a do loop.
Hope that makes better sense.

 

[PHV]

So, what have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

"The values in the cells are background colors"

This indicates to me that there are actual values in the cells and the values correspond to the interior color.

"If any are red I need to set an overall indicator in a different cell to red. Same for yellow and green."

If this is the case, why are you using any VBA? What you describe can be done with Conditional Formatting, which is an Excel spreadsheet feature.

"Beginning column/row is the same but the last row could change based on inserts."

How is this data inserted? Your answer will be informative as to whether code would be required to extend the CF or not.




[/b]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rschneeg]

Tried to attach a document but couldn't get through my firewall here.
I have a cell which is locked and calculated with forumula based on the difference between two other cells (dates) on the same row. The words green, yellow or red are inserted into the cell based on those other date cell values differences. Conditional formatting is used to set the color of the cell based on the words (red, yellow or green).
I have a fixed cell that I want to be able to set to a color based on the value (or color) of those rows of cells that are green, yellow or red. Each row could have different color and some blanks or white(default).
If any of the cells in the column range are red, I want to set the fixed cell to Red and use conditional formatting to set the color the same. If I find one(red) stop searching. Then do the same for yellow and green.
The problem I have is that the user can insert rows or populate rows with additional row information so I never know which is the last row to perform checking. There are other cells that follow with different type of information that keep getting "pushed down" if additional rows are inserted.
Hope that makes sense. I tried to insert the excel doc but was blocked by my firewall.
Here's what I'm thinking I need to do. I'm always checking column G for these green, yellow and red values. It always starts in cell G5 but not sure where the last row may end. I need to loop through all of the rows in column G and stop when I've hit the last one with this color or when I hit the next set of rows with different type of data. Either a Do Loop or a Do Until. Not being a VB person just was unclear on how to do this.
Thanks, hope that helps.

 

[PHV]

Again, what have you tried so far and where in your code are you stuck ?

 

[Gavona]

If instead of using Red, Yellow, Green you used 3, 2, 1 then you could still use that for your conditional formats and use Max(G7:G65535) to get the maximum value in column A.
No code required. Not sure that there would be much advantage in limiting the range though that is readily doable.

Gavin

 

[rschneeg]

OK, here's the code I am using.
I'm open to more efficient ways of doing this.
Is there any way I can export this macro so it can be used in all the versions of this spreadsheet?
Sub SetScheduleIndicator()
Dim r As Integer

Cells(5, 2).Value = "Green"
r = 13

Do Until Cells(r, 6).Value = "Overal Risk/Issue Status"
If Cells(r, 7).Value = "Red" And Cells(r, 8).Value <> "Y" Then
Cells(5, 2).Value = "Red"
ElseIf _
Cells(r, 7).Value = "Yellow" And Cells(5, 2).Value <> "Red" And Cells(r, 8).Value <> "Y" Then _
Cells(5, 2).Value = "Yellow"
End If

Loop
End Sub

Thanks for your assistance.