Color enitre row 1

[drrocket5292]

I'm trying to write VBA code that will allow me to compare the cells in two list and then color the cell if it is in both lists. ive written code that works but im trying to add to it so that it not only colors that one cell but the entire row that it is in. can someone please tell me what i need to add to my code to make this happen? here is my code. thanks for the help.

Sub Color()

Dim Cell As Range
Dim r As Range
Dim n As Integer

Worksheets(Array("Sheet1", "Sheet2")).Select

Set r = Worksheets("sheet2").Range("a1:a100")

For Each Cell In Worksheets("Sheet1").Range("b1:B266")
For n = 1 To r.Rows.Count
If Cell.Value = Worksheets("sheet2").Cells(n, 1) Then _
Cell.Interior.ColorIndex = 48
Next n
Next Cell

End Sub

 

[xlbo]

change
Cell.Interior.ColorIndex = 48
to
Cell.ENTIREROW.Interior.ColorIndex = 48

However.........

there are a number of better ways to achieve what you want

1: Use conditional formatting along with a VLOOKUP formula
2: if you do want to loop, minimise the amount of looping by using the FIND method - that way you only need to loop through ONE range rather than 2. If you are interested in either of these other techniques, please reply back and I will try to explain them better


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Something like this ?
Cell.EntireRow.Interior.ColorIndex = 48

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bong]

You didn't specify whether by "row" you mean just where the data is or the entire row regardless of whether there is content.

Turning on the macro recorder, and doing it both ways, one gets:

Range("A5:J5").Select
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With


for the data-only part (assuming data in columns [red]A[/red] through [red]J[/red]

and

Rows("15:15").Select
With Selection.Interior
.ColorIndex = 45
.Pattern = xlSolid
End With


for the entire row.

You can probably by-pass the Selection by using:
With Range("A5:J5").Interior
or
With Rows("15:15").Interior, respectively.

_________________
Bob Rashkin

 

[longhair]

drrocket5292,
something like:
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
will change the first row to yellow.
i leave it as an excercise for the reader to adjust the row dynamically, if need be.
regards,
longhair

 

[drrocket5292]

xlbo,
would you mind showing me how to clean this up a little using the find method? as you can tell im a beginner at this and could use a few tips. i originally tried to use conditional formatting but my lists have to be on 2 separate worksheets and it said that you cannot conditionally format across multiple worksheets. thanks.

 

[xlbo]

whilst it is true that you cannot CF across spreadsheets, you can test across spreadsheets - you could do this with a VLOOKUP in the CF - however, it can be a bit tricky to dynamically set CF so lets go with the FIND routine

The basic premis is that you only loop once (as that is slow) and take the place of the other loop with the FIND routine (which is very quick)

An outline would be:

set RangeToTest = sheets("Sheet1").Range("b1:B266")

For each c in RangeToTest
  set fCell = sheets("Sheet2").range("RangeToLookIn").find(What:=c.value, lookin:=xlvalues,lookat:=xlwhole)
    if not fCell is nothing then
      'item found
      c.entirerow.interior.colorindex = 48
    else
      'item not found - don't colour row
    end if
next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[longhair]

xlbo,
nicely explained.
a star.
regards,
longhair