In the attached file, there is a column called status. The column is normally up to 3k rows. However, they only update the yellow and red status and leave the green blank. I want a macro that will scan the column and enter "green" in the empty cells for that column... see attached...
Try this.
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
Hi,
Try this
Sub tst()
Dim myLastRow AS Long
ActiveSheet.UsedRange
myLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
For I = 1 To myLastRow
If Range("B" & I) = "" Then
Range("B" & I).Interior.Color = RGB(0, 255, 0)
End If
Next I
End Sub
Hi bakerman2 ---I get a an error: variable not defined (sn =). In my file, the column is BQ where BQ3 is the header and BQ4 is the row under it... your code works well in my sample file but not in my actual file... what am I doing wrong?
Both of the above solutions use a for next loop which could prove to be slow for a large amount of data.
Why not put a formula into an empty column
And Paste the values into your target column?
Please Login or Register to view this content.
Last edited by mehmetcik; 02-10-2017 at 03:28 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Mehmetcik: How can I make this work for column where BQ3 is the header and BQ4 is the row under it?
All you need to change to different column is this line
With .Range("BQ3:BQ" & .Cells(Rows.Count, "BQ").End(xlUp).Row)
FYI: I have already change the column
Last edited by AB33; 02-10-2017 at 04:54 PM.
@mehmetcik
True if you perform the loop on the worksheet. But as you might have noticed my loop is performed in memory and the resulting array is written to worksheet in one go so speed loss will be minimal.Both of the above solutions use a for next loop which could prove to be slow for a large amount of data.
Please Login or Register to view this content.
Last edited by jindon; 02-10-2017 at 09:56 PM.
I do not understand your Question.
Two possible meanings.
1. Your Data Column is now Column BQ not Column C as previously stated.
2. Use Column BQ as the helper ColumnPlease Login or Register to view this content.
3. On reflection there is a faster method.Please Login or Register to view this content.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks