I get a compile error on line 21 below. What is wrong with it?
I am trying to color fill a cell based on the XColor value...
Help!


0 r = 1
1 Do While ActiveSheet.Cells(r, 1) <> ""
2 str1 = ActiveSheet.Cells(r, 1)
3 str2 = ActiveSheet.Cells(r, 2)
4 comp1 = strip(str1)
5 comp2 = strip(str2)
6
7 If (comp1 <> comp2) Then
8 XColor = 5
9 Else
10 XColor = 25
11 End If
12
13 If (XColor) Then
14 Range("ActiveSheet.Cells(r, 1):ActiveSheet.Cells(r, 2)").Select
15 With Selection.Interior
16 .ColorIndex = 3
17 .Pattern = xlSolid
18 End With
19 End If
20 r = r + 1
21 Loop

Sorry - I meant on line 14!
> 0 r = 1
> 1 Do While ActiveSheet.Cells(r, 1) <> ""
> 2 str1 = ActiveSheet.Cells(r, 1)
> 3 str2 = ActiveSheet.Cells(r, 2)
> 4 comp1 = strip(str1)
> 5 comp2 = strip(str2)
> 6
> 7 If (comp1 <> comp2) Then
> 8 XColor = 5
> 9 Else
> 10 XColor = 25
> 11 End If
> 12
> 13 If (XColor) Then
> 14 Range("ActiveSheet.Cells(r, 1):ActiveSheet.Cells(r, 2)").Select
> 15 With Selection.Interior
> 16 .ColorIndex = XColor
> 17 .Pattern = xlSolid
> 18 End With
> 19 End If
> 20 r = r + 1
> 21 Loop

Change it to

Range(ActiveSheet.Cells(R, 1), ActiveSheet.Cells(R, 2)).Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Sam" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sorry - I meant on line 14!
>> 0 r = 1
>> 1 Do While ActiveSheet.Cells(r, 1) <> ""
>> 2 str1 = ActiveSheet.Cells(r, 1)
>> 3 str2 = ActiveSheet.Cells(r, 2)
>> 4 comp1 = strip(str1)
>> 5 comp2 = strip(str2)
>> 6
>> 7 If (comp1 <> comp2) Then
>> 8 XColor = 5
>> 9 Else
>> 10 XColor = 25
>> 11 End If
>> 12
>> 13 If (XColor) Then
>> 14 Range("ActiveSheet.Cells(r, 1):ActiveSheet.Cells(r,
>> 2)").Select
>> 15 With Selection.Interior
>> 16 .ColorIndex = XColor
>> 17 .Pattern = xlSolid
>> 18 End With
>> 19 End If
>> 20 r = r + 1
>> 21 Loop
>

That did the trick. Thank you.
May I impose with one more sudggestion?
In line 1, where I control the looping, I am not happy with check for
spaces, because someone could have added a space accidentally. Is there
a better way to loop until the end of all data?
Again, thanks!

Do While trim(ActiveSheet.Cells(r, 1)) <> ""

???

Sam wrote:
>
> I get a compile error on line 21 below. What is wrong with it?
> I am trying to color fill a cell based on the XColor value...
> Help!
>
> 0 r = 1
> 1 Do While ActiveSheet.Cells(r, 1) <> ""
> 2 str1 = ActiveSheet.Cells(r, 1)
> 3 str2 = ActiveSheet.Cells(r, 2)
> 4 comp1 = strip(str1)
> 5 comp2 = strip(str2)
> 6
> 7 If (comp1 <> comp2) Then
> 8 XColor = 5
> 9 Else
> 10 XColor = 25
> 11 End If
> 12
> 13 If (XColor) Then
> 14 Range("ActiveSheet.Cells(r, 1):ActiveSheet.Cells(r, 2)").Select
> 15 With Selection.Interior
> 16 .ColorIndex = 3
> 17 .Pattern = xlSolid
> 18 End With
> 19 End If
> 20 r = r + 1
> 21 Loop

--

Dave Peterson

thank you