Changing the background color of a cell

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi

I'm comparing two lists and if a contract number equals that on another list
I want to highlight the line in the original list. I have looked for ages on
the web but everywhere that uses the range command seems to assume you know
the row but I'm trying to use two variables as below.

If Trim(Worksheets("Cappuchino Data").Cells(iCappuchinoRow,
7).Value) = Trim(Worksheets("Subs Safety Net").Cells(iSubsSafetyRow,
3).Value) Then

Worksheets("Cappuchino Data").Range(Cells(iCappuchinoRow, 1),
Cells(iCappuchinoRow, 31)).Interior.Color = 1
GoTo NextCappLine

End If

However, this just throws up an error.

Can you please help?

Many Thanks

Steve
 
Hi Steve

The below code will highlight each row if 'Cappuchino Data' column G data
match with the list in Subs Safety Net").Range("C1:C10")..Try and feedback

Sub Macro()

Dim rng1 As Range, rng2 As Range, cell As Range

Set rng1 = Worksheets("Cappuchino Data").Range("G1:G10")
Set rng2 = Worksheets("Subs Safety Net").Range("C1:C10")

For Each cell In rng1
If Trim(cell.Text) <> "" Then
If Not rng2.Find(cell.Text, , xlValues, 1) Is Nothing Then
cell.Offset(0, -6).Resize(1, 31).Interior.ColorIndex = 15
End If
End If
Next

End Sub

If this post helps click Yes
 
Hi

Many thanks for your response and help

Yes it does seem to have worked (i just need to confirm), however, my only
question is, I don't always know the length of each of the lists so I can't
put the range in. In this instance, I changed the values but it would be nice
to have a generic routine that will work for any lists.

I havea routine that returns the number of lines in a list so using that
would be good.

Many Thanks

Steve
 
Steve

You can set that as
Set rng1 = Worksheets("Cappuchino Data").Range("G:G")
Set rng2 = Worksheets("Subs Safety Net").Range("C:C")

OR using named range

Set rng1 = Worksheets("Cappuchino Data").Range("namedrange1")
Set rng2 = Worksheets("Subs Safety Net").Range("namedrange2")



If this post helps click Yes
 
Back
Top