Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine the lists problem
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 | | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
another combine lists question | Excel Discussion (Misc queries) | |||
Merge/Combine address lists | Excel Worksheet Functions | |||
Please Help Combine to Two Lists | Excel Worksheet Functions | |||
How do I combine lists in multiple worksheets | Excel Worksheet Functions | |||
How do you combine lists | Excel Programming |