Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color cells that match on two sheet
Hi Everyone
Using XL2003 I'm new to programming and this is my first try beside a couple of Userform. I've got a Vacation Planner on one sheet for 17 People Second sheet is a global view showing workdays for the year for all 17 people. Each cell is numbered to match with Julian date in Calctable sheet Third sheet is my Calculation table. Taking Start Date End date and listing them, Then converting those dates in to Julian dates without the year. I would like to colour the cells on the sheet "Globalview" that match the holiday This is a sample of my code "Its not working " and just can't get it. Probably way off. ------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim GV As Range Dim CT As Range On Error GoTo ws_exit: With Worksheets("GlobalView") GV = Range("c5:bc9") End With With Worksheets("CalcTable") CT = Range("Julian") 'Range E3:E133 End With For Each cell In Worksheets("Global View").Range("GV") GV = Worksheets("CalcTable").Range("CT") cell.Interior.ColorIndex = 10 Next cell End Sub I tested everything with Conditional Formatting and it works "BUT" only 3 Condition and I need 17 The link below is a JPEG showing a snippet of the 3 sheets to give a better idea I hope http://www.mediafire.com/?zczmqzngm40 Thanking you in advance John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color cells that match on two sheet
For Each cell In Worksheets("Global View").Range("GV")
GV = Worksheets("CalcTable").Range("CT") This looks like the culprit. Try replacing it with this: For Each cell In GV.Cells cell = Worksheets("CalcTable").Range("CT") "John" wrote in message ... Hi Everyone Using XL2003 I'm new to programming and this is my first try beside a couple of Userform. I've got a Vacation Planner on one sheet for 17 People Second sheet is a global view showing workdays for the year for all 17 people. Each cell is numbered to match with Julian date in Calctable sheet Third sheet is my Calculation table. Taking Start Date End date and listing them, Then converting those dates in to Julian dates without the year. I would like to colour the cells on the sheet "Globalview" that match the holiday This is a sample of my code "Its not working " and just can't get it. Probably way off. ------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim GV As Range Dim CT As Range On Error GoTo ws_exit: With Worksheets("GlobalView") GV = Range("c5:bc9") End With With Worksheets("CalcTable") CT = Range("Julian") 'Range E3:E133 End With For Each cell In Worksheets("Global View").Range("GV") GV = Worksheets("CalcTable").Range("CT") cell.Interior.ColorIndex = 10 Next cell End Sub I tested everything with Conditional Formatting and it works "BUT" only 3 Condition and I need 17 The link below is a JPEG showing a snippet of the 3 sheets to give a better idea I hope http://www.mediafire.com/?zczmqzngm40 Thanking you in advance John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color cells that match on two sheet
Hi John,
I can't really answer your question because I don't understand exactly what you are trying to do. However, I will point out some errors in the coding. Firstly it is good practice to dimension your variables as follows. Dim GV As Range Dim CT As Range Dim cell As Range In the following code when using With you need to include the stop (.) in front of Range. Also when assigning a range to a variable you use Set. With Worksheets("GlobalView") Set GV = .Range("c5:bc9") End With As per previous comments for the following code. With Worksheets("CalcTable") Set CT = .Range("Julian") 'Range E3:E133 End With When you have assigned a range in a worksheet to a variable you only use the variable in lieu of the worksheet and range as follows. For Each cell In GV I have no idea what the following line is supposed to do but in any case it is incorrect. GV = Worksheets("CalcTable").Range("CT") However, when assigning a range to a variable you do not use CT in the format you have used it. (CT is not a named range; it is a VBA variable.) Because you have already assigned a range to CT. If you then want to assign that range to another variabe it would be as follows. Set GV = CT The following line would assign the interior color of every cell in the range GV. cell.Interior.ColorIndex = 10 Next cell In addition to the above, you need to understand that a range assigned to a variable in VBA is not the same as a named range on a worksheet. A named range on a worksheet is saved with the worksheet and is available next time the worksheet is opened. The VBA variable to which a range is assigned looses its contents. It appears that you have a named range "Julian" while GV and CT are VBA variables. To help you to actually code what you are trying to achieve, it will be necessary for you to provide a detailed description of your requirements. -- Regards, OssieMac "John" wrote: Hi Everyone Using XL2003 I'm new to programming and this is my first try beside a couple of Userform. I've got a Vacation Planner on one sheet for 17 People Second sheet is a global view showing workdays for the year for all 17 people. Each cell is numbered to match with Julian date in Calctable sheet Third sheet is my Calculation table. Taking Start Date End date and listing them, Then converting those dates in to Julian dates without the year. I would like to colour the cells on the sheet "Globalview" that match the holiday This is a sample of my code "Its not working " and just can't get it. Probably way off. ------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim GV As Range Dim CT As Range On Error GoTo ws_exit: With Worksheets("GlobalView") GV = Range("c5:bc9") End With With Worksheets("CalcTable") CT = Range("Julian") 'Range E3:E133 End With For Each cell In Worksheets("Global View").Range("GV") GV = Worksheets("CalcTable").Range("CT") cell.Interior.ColorIndex = 10 Next cell End Sub I tested everything with Conditional Formatting and it works "BUT" only 3 Condition and I need 17 The link below is a JPEG showing a snippet of the 3 sheets to give a better idea I hope http://www.mediafire.com/?zczmqzngm40 Thanking you in advance John . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color cells that match on two sheet
Thank you for your help,
My Code:"Revised but not working" I'm missing something Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim GV As Range Dim CT As Range On Error GoTo ws_exit: With Worksheets("GlobalView") GV = .Range("c5:bc9") End With With Worksheets("CalcTable") CT = .Range("Julian") 'Range E3:E133 End With For Each cell GV.cells cell = Worksheets("CalcTable").Range("CT") cell.Interior.ColorIndex = 10 Next cell End Sub ------------------------ What I'm trying to do is "Color" the cell in Worksheet ("GlobalView") that match in worksheet ("CalcTable"). Every cell in the range ("GV") in GlobalView is numbered and when a number appear in the range ("CT") on Worksheet ("Calctable") that match I want the cell in GlobalView to change color. Please let me know if you need further information Regards John "OssieMac" wrote in message ... Hi John, I can't really answer your question because I don't understand exactly what you are trying to do. However, I will point out some errors in the coding. Firstly it is good practice to dimension your variables as follows. Dim GV As Range Dim CT As Range Dim cell As Range In the following code when using With you need to include the stop (.) in front of Range. Also when assigning a range to a variable you use Set. With Worksheets("GlobalView") Set GV = .Range("c5:bc9") End With As per previous comments for the following code. With Worksheets("CalcTable") Set CT = .Range("Julian") 'Range E3:E133 End With When you have assigned a range in a worksheet to a variable you only use the variable in lieu of the worksheet and range as follows. For Each cell In GV I have no idea what the following line is supposed to do but in any case it is incorrect. GV = Worksheets("CalcTable").Range("CT") However, when assigning a range to a variable you do not use CT in the format you have used it. (CT is not a named range; it is a VBA variable.) Because you have already assigned a range to CT. If you then want to assign that range to another variabe it would be as follows. Set GV = CT The following line would assign the interior color of every cell in the range GV. cell.Interior.ColorIndex = 10 Next cell In addition to the above, you need to understand that a range assigned to a variable in VBA is not the same as a named range on a worksheet. A named range on a worksheet is saved with the worksheet and is available next time the worksheet is opened. The VBA variable to which a range is assigned looses its contents. It appears that you have a named range "Julian" while GV and CT are VBA variables. To help you to actually code what you are trying to achieve, it will be necessary for you to provide a detailed description of your requirements. -- Regards, OssieMac "John" wrote: Hi Everyone Using XL2003 I'm new to programming and this is my first try beside a couple of Userform. I've got a Vacation Planner on one sheet for 17 People Second sheet is a global view showing workdays for the year for all 17 people. Each cell is numbered to match with Julian date in Calctable sheet Third sheet is my Calculation table. Taking Start Date End date and listing them, Then converting those dates in to Julian dates without the year. I would like to colour the cells on the sheet "Globalview" that match the holiday This is a sample of my code "Its not working " and just can't get it. Probably way off. ------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim GV As Range Dim CT As Range On Error GoTo ws_exit: With Worksheets("GlobalView") GV = Range("c5:bc9") End With With Worksheets("CalcTable") CT = Range("Julian") 'Range E3:E133 End With For Each cell In Worksheets("Global View").Range("GV") GV = Worksheets("CalcTable").Range("CT") cell.Interior.ColorIndex = 10 Next cell End Sub I tested everything with Conditional Formatting and it works "BUT" only 3 Condition and I need 17 The link below is a JPEG showing a snippet of the 3 sheets to give a better idea I hope http://www.mediafire.com/?zczmqzngm40 Thanking you in advance John . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color cells that match on two sheet
Thank you both for your help,
My Code:"Revised but not working" I'm missing something Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim GV As Range Dim CT As Range On Error GoTo ws_exit: With Worksheets("GlobalView") GV = .Range("c5:bc9") End With With Worksheets("CalcTable") CT = .Range("Julian") 'Range E3:E133 End With For Each cell GV.cells cell = Worksheets("CalcTable").Range("CT") cell.Interior.ColorIndex = 10 Next cell End Sub ------------------------ What I'm trying to do is "Color" the cell in Worksheet ("GlobalView") that match in worksheet ("CalcTable"). Every cell in the range ("GV") in GlobalView is numbered and when a number appear in the range ("CT") on Worksheet ("Calctable") that match I want the cell in GlobalView to change color. Please let me know if you need further information Regards John "John" wrote in message ... Hi Everyone Using XL2003 I'm new to programming and this is my first try beside a couple of Userform. I've got a Vacation Planner on one sheet for 17 People Second sheet is a global view showing workdays for the year for all 17 people. Each cell is numbered to match with Julian date in Calctable sheet Third sheet is my Calculation table. Taking Start Date End date and listing them, Then converting those dates in to Julian dates without the year. I would like to colour the cells on the sheet "Globalview" that match the holiday This is a sample of my code "Its not working " and just can't get it. Probably way off. ------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim GV As Range Dim CT As Range On Error GoTo ws_exit: With Worksheets("GlobalView") GV = Range("c5:bc9") End With With Worksheets("CalcTable") CT = Range("Julian") 'Range E3:E133 End With For Each cell In Worksheets("Global View").Range("GV") GV = Worksheets("CalcTable").Range("CT") cell.Interior.ColorIndex = 10 Next cell End Sub I tested everything with Conditional Formatting and it works "BUT" only 3 Condition and I need 17 The link below is a JPEG showing a snippet of the 3 sheets to give a better idea I hope http://www.mediafire.com/?zczmqzngm40 Thanking you in advance John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
make all cells in same row match cell formatted with fill color | Excel Discussion (Misc queries) | |||
Copy a value from one sheet if two cells match | Excel Discussion (Misc queries) | |||
Check if 2 cells match on another sheet then lookup value | Excel Worksheet Functions | |||
Copying a column to a new sheet so that cells match. | Excel Worksheet Functions |