ExcelBanter - Combine the lists problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Combine the lists problem (https://www.excelbanter.com/excel-programming/309373-combine-lists-problem.html)

Brian

Combine the lists problem

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

Combine the lists problem

...Patrick[_5_]

Combine the lists problem

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" a écrit dans le message de
...
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

Brian

Combine the lists problem

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.

"Jim Rech" wrote in message
...
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
"Brian" wrote in message
...
|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
|
|
|

Brian

Combine the lists problem

Brian

Combine the lists problem

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?

"Jim Rech" wrote in message
...
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
"Brian" wrote in message
...
|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
|
|
|

All times are GMT +1. The time now is 12:05 AM.