I have 2 spreadsheets with identical data. In column B of RED.XLS, certain
items are highlighted in red text. In the same column of GREEN.XLS, certain
other items are highlighted in green text. Some items are highlighted in
both spreadsheets.

In COMBINED.XLS, I have another copy of the column B data. There I want to
highlight in red any item which is highlighted in either RED.XLS or
GREEN.XLS. I also want to identify the data items which are highlighted in
both of the other spreadsheets, by changing the background color of the cell
to the right of the cell concerned.

The following code crashes at this line, and I can't see why:

WS3.Range(Cell.Offset(0, 1)).Interior.Color = 5

What am I doing wrong?

I am using XL97 on W2000.

Sub RedAndGreen()
' 12/9/04
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim Cell As Range
Set WS1 = Workbooks(" red.xls").ActiveSheet
Set WS2 = Workbooks("green.xls").ActiveSheet
Set WS3 = Workbooks("combined.xls").ActiveSheet
For Each Cell In WS1.Columns(2).SpecialCells(xlCellTypeFormulas)
If Cell.Font.ColorIndex = 3 Then
If WS2.Range(Cell.Address).Font.ColorIndex = 4 Then
WS3.Range(Cell.Offset(0, 1)).Interior.Color = 5
Else: WS3.Range(Cell.Address).Font.ColorIndex = 3
End If
End If
Next
For Each Cell In WS2.Columns(2).SpecialCells(xlCellTypeFormulas)
If Cell.Font.ColorIndex = 4 Then
WS3.Range(Cell.Address).Font.ColorIndex = 3
End If
Next
End Sub

Maybe you wanted:

WS3.Range(Cell.Offset(0, 1).address).Interior.Color = 5



Brian wrote:

I have 2 spreadsheets with identical data. In column B of RED.XLS, certain
items are highlighted in red text. In the same column of GREEN.XLS, certain
other items are highlighted in green text. Some items are highlighted in
both spreadsheets.

In COMBINED.XLS, I have another copy of the column B data. There I want to
highlight in red any item which is highlighted in either RED.XLS or
GREEN.XLS. I also want to identify the data items which are highlighted in
both of the other spreadsheets, by changing the background color of the cell
to the right of the cell concerned.

The following code crashes at this line, and I can't see why:

WS3.Range(Cell.Offset(0, 1)).Interior.Color = 5

What am I doing wrong?

I am using XL97 on W2000.

Sub RedAndGreen()
' 12/9/04
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim Cell As Range
Set WS1 = Workbooks(" red.xls").ActiveSheet
Set WS2 = Workbooks("green.xls").ActiveSheet
Set WS3 = Workbooks("combined.xls").ActiveSheet
For Each Cell In WS1.Columns(2).SpecialCells(xlCellTypeFormulas)
If Cell.Font.ColorIndex = 3 Then
If WS2.Range(Cell.Address).Font.ColorIndex = 4 Then
WS3.Range(Cell.Offset(0, 1)).Interior.Color = 5
Else: WS3.Range(Cell.Address).Font.ColorIndex = 3
End If
End If
Next
For Each Cell In WS2.Columns(2).SpecialCells(xlCellTypeFormulas)
If Cell.Font.ColorIndex = 4 Then
WS3.Range(Cell.Address).Font.ColorIndex = 3
End If
Next
End Sub

--

Dave Peterson

Many thanks.

"Dave Peterson" wrote in message
...
Maybe you wanted:

WS3.Range(Cell.Offset(0, 1).address).Interior.Color = 5



Brian wrote:

I have 2 spreadsheets with identical data. In column B of RED.XLS,
certain
items are highlighted in red text. In the same column of GREEN.XLS,
certain
other items are highlighted in green text. Some items are highlighted in
both spreadsheets.

In COMBINED.XLS, I have another copy of the column B data. There I want
to
highlight in red any item which is highlighted in either RED.XLS or
GREEN.XLS. I also want to identify the data items which are highlighted
in
both of the other spreadsheets, by changing the background color of the
cell
to the right of the cell concerned.

The following code crashes at this line, and I can't see why:

WS3.Range(Cell.Offset(0, 1)).Interior.Color = 5

What am I doing wrong?

I am using XL97 on W2000.

Sub RedAndGreen()
' 12/9/04
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim Cell As Range
Set WS1 = Workbooks(" red.xls").ActiveSheet
Set WS2 = Workbooks("green.xls").ActiveSheet
Set WS3 = Workbooks("combined.xls").ActiveSheet
For Each Cell In WS1.Columns(2).SpecialCells(xlCellTypeFormulas)
If Cell.Font.ColorIndex = 3 Then
If WS2.Range(Cell.Address).Font.ColorIndex = 4 Then
WS3.Range(Cell.Offset(0, 1)).Interior.Color = 5
Else: WS3.Range(Cell.Address).Font.ColorIndex = 3
End If
End If
Next
For Each Cell In WS2.Columns(2).SpecialCells(xlCellTypeFormulas)
If Cell.Font.ColorIndex = 4 Then
WS3.Range(Cell.Address).Font.ColorIndex = 3
End If
Next
End Sub

--

Dave Peterson