VBA macro find blank RGB(0, 255, 0) colored cells in range and put in 1 value

Hi,

I have a problem with my search blowing up with a server error so while I'd like to benefit from all the other expertise before reinventing the wheel I'm resigned to ask for help. I know there's lots of wonderful help here but I apologize for my search problems in advance. Sorry to vent.

SO HERE IS WHAT I NEED.
I need a macro I can run on my active worksheet in cell range N:17 to NN:300 that will search for cells with cell color RGB(0, 255, 0) and insert the value 1 in them so I can make use of my SUMIFS formula outside of this range which looking for certain row conditions and determining totals.


In back ground; For a previous attempt without programming I tried the fill blank cells in the range it filled all the cells. That was good but when I tried the search and replace and choosing cell format it would not find a value. With out the format it found all the 1s but once I selected format no results regardless of color. I looked all over online and Excel's finicky find is well known in later generations. It seems simple enough but strange response.

Anyway I'd appreciate it if someone had a simple macro for this.

Gratitude in advance!!

YasserKhalil,

Thank you for your response. I will try yours now. I had gotten as far as the code below while waiting and it does work part way. Meaning that it hangs up the program to where I can't do anything while it's running and then have to hit Ctrl + Break. In result after the Ctrl + Break it does put 1's in the correct cells half way down and then hangs. Yet all the cell values are empty and the mix of same blue, green, and no fill boxes is all similar. Any thoughts? I will try your but curious why mine does not work.

Please Login or Register  to view this content.

Thank you sir.

Maybe:

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

Ok so overwhelming and wonderful responses from all. I have had a chance to try beyond John H. Davis' response. That one does work. It consumes the resources of excel completely like my first one but it does complete and work well. I did go back in and turn screen update off then back on again thinking that might help. So can anyone tell me from all of the responders is the reason excel hangs really because there are so many cells to check a put a value in. Is there more efficiency some other way. Maybe I need to take the time to evaluate the remaining generous responses but I don't want to lose my audience with my blazing speed of initiation here. I'm not real fast but I can lift heavy things. Just kidding. Just older and slower than I used to be. I am not real good at VBA and so reading the additional code taek a while.

Thanks all, your help is tremendously appreciated!

Winon,

Fast is what I'm looking for. I will try it. The last comment I made was a slow type and so it got inserted after your reply. Thank you all again. You guys are awesome!!

Winon,

Maybe, I do have a knack for breaking computers even with 16GB ram but the processor is i5 and I can't get work to replace it. I apologize for the time away. I had the Sucuri CloudProxy - Backend Server timeout issue after my last response hung up. Then it wouldn't let me back in from any of the reply links in my email. I forgot to try to go direct to home page but it worked and here I am. So I will try your Winon. As far as I'm concerned you all deserve and get reputation.

Thank you!!! Your awesome!



P.S. As mentioned at the start of my post I've got a screen shot of my find cell with format issue after I get this thoroughly resolved by attempting the faster solution. I'll thread that one after a bit in the non VBA forum.

Winon,

I need help here if you could. I'm not sure what's missing but I get the Run-time error '91 or Object variable or With block variable not set. I attached a screen shot where the code hangs up. Thank you sir.

I reopened it and now the worksheet wont' come up even after trying arrange all tiled. I can get to the VB script since the command header is visible. I'm working to get it back.

@ Winon,
I am going to have to rebuild the sheet. It's corrupt somehow. I'll copy the code and retry your code and report back. If you could check back next opportunity sir I may then have an update available.

Winon,

Yes it was very fast. Amazingly different. Only issue is it turned every cell in the range green and filled it with 1s. Not exactly what I had in mind BUT it was very fast! lol. Ok I will go back to the slow code for now and see if I can figure out how it did that and learn something. Thank you sir.

@Arkadi,

Thanks for the insite. Maybe I'll look at it again and see why that is. Thank you sir.

Winon,

The new code worked wonderfully well!! It is very fast and I am very glad to get past slow mode code. Thanks so much for your help and contribution to solving my code problem. I think I understand what it is doing. It is amazing the number of ways the code can be written. Thanks for helping me learn.


I would like to see search and replace work as intended it would have saved a lot of effort from everyone but there's a challenge and a laugh in every opportunity.


Also thanks again to all other contributors!

@JOHN H. DAVIS , @YasserKhalil , @Arkadi