Updating macro functionality

detweiler

Board Regular
Joined
Aug 2, 2013
Messages
60
Okay, so really weird, because I thought I had received help with this macro here, but found that there weren't any replies to my initial request ( ? ).

So, I have this macro -

Dim c As Range, d As String
For Each c In Range("J2", Range("J" & Rows.Count).End(xlUp))
If c.Value Like "*[0-9]*" Then
d = Range("J" & c.Row & ":P" & c.Row).End(xlToRight)
c.Value = IIf(d <> "", d, c)
End If
Next

What I have been trying to find is some variant that I could frankenvba into the code so that when it comes across a cell in "J" that is empty, it fills it with a color.

Comments, suggestions and thoughts are welcomed.

Much thanks.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
How about
Code:
Sub detweiler()
   Range("J:J").SpecialCells(xlBlanks).Interior.Color = 45678
End Sub
Also where is the other thread, as I can't find it?
 
Upvote 0
Solution
Here's my original posting.

Is there a way to incorporate that into the existing macro so it's processing the column at once, or is it better to be separated out like that?
 
Upvote 0
Okay, so really weird, because I thought I had received help with this macro here, but found that there weren't any replies to my initial request ( ? ).
Perhaps you also posted it in another forum, and got help there.

Note if you do that (known as "Cross-Posting"), that we ask that you follow our guidelines on Cross-Posting, as mentioned in rule 13 here: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Thanks
 
Upvote 0
You can add it to your existing code, or keep it separate. Which ever you prefer.

Thanks for the link & I have now closed that thread.
 
Upvote 0
Did that once before, got called out on it and decided to keep it simple by staying in this forum.
 
Upvote 0
Am I correct in surmising that the macro will fill all of the blank/empty cells in column J?

If so, I apologize for not being more specific with my inquiry about incorporating code into the existing macro. The reason for wanting to add the additional code is so that it would only fill those blank/empty cells that fell within the range returned from counting cells with data in column J ( For Each c In Range("J2", Range("J" & Rows.Count)... ).

Appreciate the input and time.
 
Upvote 0
It will only fill blank cells within the usedrange.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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