Combine the lists problem

[Brian]

I have a spreadsheet with 3 tabs, named "Red", "Green" and "Combined". The
data in columns A and B is the same on each tab, except that certain items
in column B of "Red" have text highlighted in Red, certain other items in
Column B of "Green" have text highlighted in green, while none of the text
in "Combined" is highlighted.

If an item in Column B of either "Red" or "Green" is highlighted, I want to
have the corresponding item of "Combined" highlighted in red. I was hoping
that the following macro would do it, but when I run it, it does not crash,
but it also appears to do nothing at all.

Can anyone help?


Sub MergeColors()
'
' MergeColors Macro
' Macro recorded 16/04/2004 by bclarke
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim Cell As Range
Set WS1 = Workbooks("£ account combined.xls").Sheets("Red")
Set WS2 = Workbooks("£ account combined.xls").Sheets("Green")
Set WS3 = Workbooks("£ account combined.xls").Sheets("Combined")
For Each Cell In WS1.Columns(2).SpecialCells(xlCellTypeConstants)
If Cell.Font.ColorIndex = 3 Then
WS3.Range(Cell.Address).Font.ColorIndex = 3
End If
Next
For Each Cell In WS2.Columns(2).SpecialCells(xlCellTypeConstants)
If Cell.Font.ColorIndex = 4 Then
WS3.Range(Cell.Address).Font.ColorIndex = 3
End If
Next
End Sub

 

[Jim Rech]

Works for me, Brian. Why don't you step through the code and see what
happens when you hit a red or green cell.

--
Jim Rech
Excel MVP
|I have a spreadsheet with 3 tabs, named "Red", "Green" and "Combined". The
| data in columns A and B is the same on each tab, except that certain items
| in column B of "Red" have text highlighted in Red, certain other items in
| Column B of "Green" have text highlighted in green, while none of the text
| in "Combined" is highlighted.
|
| If an item in Column B of either "Red" or "Green" is highlighted, I want
to
| have the corresponding item of "Combined" highlighted in red. I was hoping
| that the following macro would do it, but when I run it, it does not
crash,
| but it also appears to do nothing at all.
|
| Can anyone help?
|
|
| Sub MergeColors()
| '
| ' MergeColors Macro
| ' Macro recorded 16/04/2004 by bclarke
| '
| ' Keyboard Shortcut: Ctrl+Shift+M
| '
| Dim WS1 As Worksheet
| Dim WS2 As Worksheet
| Dim WS3 As Worksheet
| Dim Cell As Range
| Set WS1 = Workbooks("£ account combined.xls").Sheets("Red")
| Set WS2 = Workbooks("£ account combined.xls").Sheets("Green")
| Set WS3 = Workbooks("£ account combined.xls").Sheets("Combined")
| For Each Cell In WS1.Columns(2).SpecialCells(xlCellTypeConstants)
| If Cell.Font.ColorIndex = 3 Then
| WS3.Range(Cell.Address).Font.ColorIndex = 3
| End If
| Next
| For Each Cell In WS2.Columns(2).SpecialCells(xlCellTypeConstants)
| If Cell.Font.ColorIndex = 4 Then
| WS3.Range(Cell.Address).Font.ColorIndex = 3
| End If
| Next
| End Sub
|
|
|

 

[...Patrick]

Hello,

i tested your code, and it works !!!!

the sheets 2 have the FONT in red , change by interior.colorindex tu have a
better vue ...


--
....Patrick
Quoi que vous fassiez, faites le bien .
Mail: http://cerbermail.com/?KPW0tTCjFw
Connectez vous sur ce forum par :
news://msnews.microsoft.com/microsoft.public.fr.excel

 

[Brian]

Thanks for your response.

I tried stepping through the code, and it only goes through each For - Next
loop about 3 times. (There are about 16,500 rows of data.). At the weekend I
will have some time to do more testing.

I set up 10 rows of test data, and the code worked for me, too, on that.

Some of the cells in Column B of the original data are blank, but I can't
see why that should make any difference.

 

[Brian]

Thanks for your response.

I tried stepping through the code, and it only goes through each For - Next
loop about 3 times. (There are about 16,500 rows of data.). At the weekend I
will have some time to do more testing.

I set up 10 rows of test data, and the code worked for me, too, on that.

Some of the cells in Column B of the original data are blank, but I can't
see why that should make any difference.

 

[Brian]

I seem to have found the answer, but I have no idea why it's behaving like
this.

Column B of "Red" and "Green" actually contain formulas referencing cells to
the right. I tried replacing the formulas with values, and then the code
worked!

How is this happening?