Macro that can fill up entire row with color if condition is true

**Skroxzim** · 02-07-2019, 08:31 AM

Hello everyone,

I am new to this VBA Excel thingy but I am loving it. I still dont know a lot of it but I figured someone might be able to help me out with a couple macros that I have been working on.

1º So the first macro that I am trying to do is:

Sub LocalizarComp()

Código = InputBox("Qual o código a ser localizado?")

Dim myrange As Range

Set myrange = ThisWorkbook.Worksheets("Localizacao").UsedRange

For Each cell In myrange.Cells

If cell.Value = Código Then

cell.Interior.Color = 184204228

End If

Next

End Sub

What the macro does now is if the code I put in the InputBox is true, it will fill up the cell with the color mentioned above. What I actually want the macro to do is if the code is true, select the entire row until last row cell being used and fill all the cell with the color mentioned (Lets say it goes until row L). It might not be as hard but since I am new it's kinda complicated for me.

Thanks for your time,

**Olly** · 02-07-2019, 08:45 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**Roel Jongman** · 02-07-2019, 08:52 AM

Try the following code.

Please Login or Register  to view this content.

if you want to "paint" more then one cell you need Cells to setup the paintrange..
cells.row = to make sure to paint the row the for each loop is currently on
1 = for column 1 of column A.
.resize will select multiple cells in this case 1 row high and X columns wide based on the Columns.count

**Skroxzim** · 02-07-2019, 11:16 AM

any help please?

**AliGW** · 02-07-2019, 12:17 PM

Be patient.

**Roel Jongman** · 02-07-2019, 12:37 PM

From the pm I got from you I understand you need a little different code because inputbox result is only lookup in column A and you want to color all cells that have a value filled but the last column filled can vary per row

Lets see if this code works better.

Please Login or Register  to view this content.

**Skroxzim** · 02-07-2019, 12:45 PM

Originally Posted by Roel Jongman

From the pm I got from you I understand you need a little different code because inputbox result is only lookup in column A and you want to color all cells that have a value filled but the last column filled can vary per row

Lets see if this code works better.

Please Login or Register  to view this content.

Ok so what I meant with the lookup is the code that I am gonna put on inputbox can only be found in column A, idk if that helps or not but I wont be putting something that can be found per say on column C, or D.

With the code that I started with it will look up for the code I put on the inputbox and will fill up cell with color mentioned above, what I want is to find the cell with the inputbox code and color from column A to column L (as shown in the picture, is the last cell being used, not necessarily going to be the last one being written on tho.

**Skroxzim** · 02-07-2019, 12:49 PM

Originally Posted by Roel Jongman

From the pm I got from you I understand you need a little different code because inputbox result is only lookup in column A and you want to color all cells that have a value filled but the last column filled can vary per row

Lets see if this code works better.

Please Login or Register  to view this content.

btw that code did not work,

here is why:

**Roel Jongman** · 02-07-2019, 01:51 PM

Oh, boy the joy of VBA, took a while to figure out my mistake..
(I used .End(xlRight) and must be .End(xlToRight) )

try this one..

Please Login or Register  to view this content.

**Skroxzim** · 02-07-2019, 01:57 PM

Originally Posted by Roel Jongman

Oh, boy the joy of VBA, took a while to figure out my mistake..
(I used .End(xlRight) and must be .End(xlToRight) )

try this one..

Please Login or Register  to view this content.

Thanks for the reply Roel, it worked better but still not exactly what I want, I think that the easiest way would be just to fill up cells until column "N" since some of the cells does not contain data on it. I think thats what it happened to the the code you made, it will stop filling up cells until last one used maybe?

**Skroxzim** · 02-07-2019, 01:58 PM

*forgot to upload image on last post sorry

**Roel Jongman** · 02-07-2019, 02:27 PM

ok, for it to go to fixed columns A:N

replace the cells line with

Please Login or Register  to view this content.

14 will make it go to N
if you want shorter or longer (before you mentioned column L) you can change that number.

If this still does not work than you will have to upload an example file (excel workbook)
where you than also show the result painted manually on a few example rows. because we are now going back and forth a lot on what looks like a simple problem..

**Skroxzim** · 02-07-2019, 02:35 PM

HEY IT WORKS PERFECT NOW! Thank you so much for your patience with me! Really glad you could help me out with this

Now lets try helping me with my other macro haha, jkjk but if any of you are trying to help the thread name is "Macro that will update changed data from one sheet to another sheet" :>

**Roel Jongman** · 02-07-2019, 03:00 PM

Ok thats great, glad to help..

If this thread is solved you can mark it as solved by going to the gray bar just above your opening post on the right side there is a menu called "Thread tools" click it and then click the Thread is solved option..
then everybody is happy

**AliGW** · 02-07-2019, 03:15 PM

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Macro that can fill up entire row with color if condition is true

LinkBack

Thread Tools

Rate This Thread

Display

Macro that can fill up entire row with color if condition is true

Re: Help me with a couple Macros

Re: Help me with a couple Macros

Macro that can fill up entire row with color if condition is true

Re: Macro that can fill up entire row with color if condition is true

Re: Macro that can fill up entire row with color if condition is true

Re: Macro that can fill up entire row with color if condition is true

Re: Macro that can fill up entire row with color if condition is true

Re: Macro that can fill up entire row with color if condition is true

Re: Macro that can fill up entire row with color if condition is true

Re: Macro that can fill up entire row with color if condition is true

Re: Macro that can fill up entire row with color if condition is true

Re: Macro that can fill up entire row with color if condition is true

Re: Macro that can fill up entire row with color if condition is true

Re: Macro that can fill up entire row with color if condition is true

Thread Information

Users Browsing this Thread

Similar Threads

A couple of questions

a couple of questions

A couple of questions...

A couple of questions...

[SOLVED] Couple more questions...

Bookmarks

Bookmarks

Posting Permissions