Color cells that match on two sheet

[John]

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

 

[JLGWhiz]

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")

 

[OssieMac]

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.

 

[John]

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

 

[John]

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