VBA highlighting rows

Question

I am trying to create a code to iterate over an excel document. I want the macro to highlight all rows based on the value to the left of the cell I am checking. This is what I'm trying to say, but can't properly in code.

For x = 4 to 3918
    if (x,2) contains the letters "LW" then
        look at (x,1)
        highlight all rows with value = (x,1) green

Please help, thank you.

it can be done with conditional formatting, is vba a requirement? — Scott Craner
– Scott Craner, Commented Jun 27, 2016 at 19:15

Matt Cremeens · Accepted Answer · 2016-06-27 19:52:51Z

1

While I can't be sure, I believe you want to translate your pseudocode like so

for x = 4 to 3918
    if Instr(cells(x, 2), "LW") then
        numToFind = cells(x, 1)
        for y = 4 to 3918
            if cells(y, 1) = numToFind then
                cells(y, 1).entirerow.Interior.ColorIndex = 4
            end if
        next y
    end if
next x

Am I reading you right?

edited Jun 27, 2016 at 19:52

answered Jun 27, 2016 at 19:19

Matt Cremeens

5,2118 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ROCKYIII Over a year ago

I tried this code. It is close in that it highlights all the rows with the "LW" in (x,2). However, it does not read the cell to the left of (x,2), find all rows with value (x,1) and highlight those cells.

findwindow Over a year ago

@ROCKYIII find all rows with value (x,1) makes no sense given that x changes...

Matt Cremeens Over a year ago

@ROCKYIII You want to find all rows with value (x,1). Can that value be anywhere in a row?

ROCKYIII Over a year ago

I'm sorry, I'm new. What I meant is, look 1 cell left of the cell with "LW" in it. That will have a value like "33" for example. Find all rows with that "33" and highlight them green.

Matt Cremeens Over a year ago

@RockyIII Where else should we look for that value of 33? Where in the row? Anywhere in the row or in a particular column?

|

Collectives™ on Stack Overflow

VBA highlighting rows

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related