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

if Excel 2003 then change

Color

to

ColorIndex

On 9 Lis, 11:49, Steve <St...@discussions.microsoft.com> wrote:
> 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 ageson
> 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
---------------
Jacob Skaria


"Steve" wrote:

> 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

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" wrote:

> 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

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
---------------
Jacob Skaria


"Steve" wrote:

> 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" wrote:
>
> > 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