Syntax Error

[John]

Hi Everyone
I got these code from "Allexpert.com" that I will modify for my use if I can,but
it's giving me a "Syntax Error" at the "IF" statement.
Playing around I always get a error at the "IF" or the "For" statement
Can anyone help me

Sub ChangeColor()
Dim LastRow_1 As Integer
Dim LastCol_1 As Integer
Dim Data_1 As Range
Dim LastRow_2 As Integer
Dim LastCol_2 As Integer
Dim Data_2 As Range

Dim Sh_1 As Worksheet
Dim Sh_2 As Worksheet

Dim X As Long
Dim Y As Long
Dim C_1 As Range
Dim C_2 As Range

Set Sh_1 = ActiveWorkbook.Sheets("Sheet1")
Set Sh_2 = ActiveWorkbook.Sheets("Sheet2")

LastRow_1 = Sh_1.Range("A65535").End(xlUp).Row
LastCol_1 = Sh_1.Range("IV1").End(xlToLeft).Column
Set Data_1 = Sh_1.Range("A1").Resize(LastRow_1, LastCol_1)

LastRow_2 = Sh_2.Range("A65535").End(xlUp).Row
LastCol_2 = Sh_2.Range("IV1").End(xlToLeft).Column
Set Data_2 = Sh_2.Range("A1").Resize(LastRow_2, LastCol_2)

For Each C_1 In Data_1 (Problem area)
For Each C_2 In Data_2
If C_2 = C_1 then '(Giving Syntax Error)
Interior.ColorIndex = 12
End if
Next C_1
Next C_2
End Sub

 

[Ryan H]

There are a few issues.

1.) C_1 and C_2 are ranges. I am assuming you are wanting to compare there
values so I added .Value to C_1 and C_2.

2.) The C_2 loop has to finish before C_1 can continue so I change the Next
C_2 to Next C_1 and visa versa.

3.) You last row code will only find the last row in Excel 2003, but in 2007
there are over 2,000,000 rows so your current code will not beable to find
the last row. So I changed it to work with any Excel version. I did the
same for the columns.

4.) You have to reference a range to change the interior color. I assumed
you wanted to change C_2 range so I added the reference.

Hope this helps! If so, let me know, click "YES" below.

Sub ChangeColor()

Dim LastRow_1 As Integer
Dim LastCol_1 As Integer
Dim Data_1 As Range
Dim LastRow_2 As Integer
Dim LastCol_2 As Integer
Dim Data_2 As Range

Dim Sh_1 As Worksheet
Dim Sh_2 As Worksheet

Dim X As Long
Dim Y As Long
Dim C_1 As Range
Dim C_2 As Range

Set Sh_1 = ActiveWorkbook.Sheets("Sheet1")
Set Sh_2 = ActiveWorkbook.Sheets("Sheet2")

LastRow_1 = Sh_1.Range(Cells.Count, "A").End(xlUp).Row
LastCol_1 = Sh_1.Range("IV1").End(xlToLeft).Column
Set Data_1 = Sh_1.Range("A1").Resize(LastRow_1, LastCol_1)

LastRow_2 = Sh_2.Range(Cells.Count, "A").End(xlUp).Row
LastCol_2 = Sh_2.Range("IV1").End(xlToLeft).Column
Set Data_2 = Sh_2.Range("A1").Resize(LastRow_2, LastCol_2)

For Each C_1 In Data_1
For Each C_2 In Data_2
If C_2.Value = C_1.Value Then
Interior.ColorIndex = 12
End If
Next C_2
Next C_1

End Sub

 

[John]

Hi Ryan
Thank you, thank you
It's working fine.
I made small modification but that's my fault, I didn't give you all the proper
information.
I'm using XL03 and I want C_1 only to change color.
Now I need to modify it for my real application.
Will post back if I need more help
Thanks Again
Happy New Year
John