Hi

I would like to create the following....

In Column D if cell value is between 0 and 10 then its row in column
B, C, D, E and F should be green;
In Column D if cell value is between 11 and 20 then column B, C, D, E
and F of the same row should be blue;
In Column D if cell value is between 21 and 30 then column B, C, D, E
and F of the same row should be red;
In Column D if cell value is between 31 and 40 then column B, C, D, E
and F of the same row should be yellow;
In Column D if cell value is between 41 and 50 then column B, C, D, E
and F of the same row should be orange;
etc

The same rule to apply for Columns Q, R,S, T and U

The worksheet is called 'Team 8'

I'm assuming this can only be performed in Macros so how do I begin?
Right click on Team 8 worksheet tab select 'View Code' and then I'm
unsure what to enter after that?

Can some one assist

Gunjani

You're headed in the right direction. As you said, Right-click on Team8 tab
and select view code, then paste this into it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyRange As Range
Dim selectedColor As Integer

If Target.Column < 4 Or Target.Cells.Count 1 Then
Exit Sub ' not in column D or multiple cells chosen (as in deleted)
End If
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target
Case Is < 0
'otherwise negative #s are green
selectedColor = xlNone
Case Is <= 10
selectedColor = 4 ' Green
Case Is <= 20
selectedColor = 41 ' Blue
Case Is <= 30
selectedColor = 3 ' Red
Case Is <= 40
selectedColor = 6 ' Yellow
Case Is <= 50
selectedColor = 45 ' Orange
Case Else
selectedColor = xlNone '
End Select
End If
Set anyRange = Union(Range("B" & Target.Row & ":E" & Target.Row), _
Range("Q" & Target.Row & ":U" & Target.Row))
anyRange.Interior.ColorIndex = selectedColor
End Sub

You didn't say what to do if values were less than zero or greater than 50,
so I've set things up that in those instances, previous coloring is removed.

"Gunjani" wrote:

Hi

I would like to create the following....

In Column D if cell value is between 0 and 10 then its row in column
B, C, D, E and F should be green;
In Column D if cell value is between 11 and 20 then column B, C, D, E
and F of the same row should be blue;
In Column D if cell value is between 21 and 30 then column B, C, D, E
and F of the same row should be red;
In Column D if cell value is between 31 and 40 then column B, C, D, E
and F of the same row should be yellow;
In Column D if cell value is between 41 and 50 then column B, C, D, E
and F of the same row should be orange;
etc

The same rule to apply for Columns Q, R,S, T and U

The worksheet is called 'Team 8'

I'm assuming this can only be performed in Macros so how do I begin?
Right click on Team 8 worksheet tab select 'View Code' and then I'm
unsure what to enter after that?

Can some one assist

Gunjani

On 18 Feb, 01:29, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
You're headed in the right direction. As you said, Right-click on Team8 tab
and select view code, then paste this into it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyRange As Range
Dim selectedColor As Integer

If Target.Column < 4 Or Target.Cells.Count 1 Then
Exit Sub ' not in column D or multiple cells chosen (as in deleted)
End If
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target
Case Is < 0
'otherwise negative #s are green
selectedColor = xlNone
Case Is <= 10
selectedColor = 4 ' Green
Case Is <= 20
selectedColor = 41 ' Blue
Case Is <= 30
selectedColor = 3 ' Red
Case Is <= 40
selectedColor = 6 ' Yellow
Case Is <= 50
selectedColor = 45 ' Orange
Case Else
selectedColor = xlNone '
End Select
End If
Set anyRange = Union(Range("B" & Target.Row & ":E" & Target.Row), _
Range("Q" & Target.Row & ":U" & Target.Row))
anyRange.Interior.ColorIndex = selectedColor
End Sub

You didn't say what to do if values were less than zero or greater than 50,
so I've set things up that in those instances, previous coloring is removed.



"Gunjani" wrote:
Hi

I would like to create the following....

In Column D if cell value is between 0 and 10 then its row in column
B, C, D, E and F should be green;
In Column D if cell value is between 11 and 20 then column B, C, D, E
and F of the same row should be blue;
In Column D if cell value is between 21 and 30 then column B, C, D, E
and F of the same row should be red;
In Column D if cell value is between 31 and 40 then column B, C, D, E
and F of the same row should be yellow;
In Column D if cell value is between 41 and 50 then column B, C, D, E
and F of the same row should be orange;
etc

The same rule to apply for Columns Q, R,S, T and U

The worksheet is called 'Team 8'

I'm assuming this can only be performed in Macros so how do I begin?
Right click on Team 8 worksheet tab select 'View Code' and then I'm
unsure what to enter after that?

Can some one assist

Gunjani- Hide quoted text -

- Show quoted text -

Thank you but I cannot get it working!!

Once it is pasted into the worksheet how do i run it, do I just save
the worksheet?

Further more I misinformed you, I wish the same rules to apply to
column Q, R, S, T and U based on the values in COLUMN S.

For future amendements can u explain

1) What does this line refer to 'If Target.Column < 4 Or
Target.Cells.Count 1 ' i.e is target column set at 4 becoz it s
reference is column D? and target cell count1 becoz it starts from
cell 1 (if so my table starts from Row 5)

2) If I wish to change the colour codes, where may I get the Colour
refernce no, i.e 4 (green), 41 ( blue)

3) Case Is < 0
'otherwise negative #s are green
Does this mean negative numbers will change to green?, if so I like to
change it to grey

4) I assume the following is based on value of Column D...
Set anyRange = Union(Range("B" & Target.Row & ":E" & Target.Row), _
Range("Q" & Target.Row & ":U" & Target.Row))
anyRange.Interior.ColorIndex = selectedColor

I wish the colours in to change in columns B to F to be determined by
the value in Column D, and the colours in columns Q to U to be
determined by Column S values.

5) What amendments would I need to make if, later, I wish to rearrange
the table so that columns are shifted to the right by say 2 columns
i.e values in Column D is in column F etc.

Thank you for ur assistance

If you have placed the code into the worksheet's code area and macros are
enabled, then it should automatically run when you type a value from 0
through 50 into any cell in column D.

Your questions:
#1) Yes, the 4 is there because column D is the 4th column on a sheet. If
you moved things two columns right (using F as the test column) then 6 would
be the value to use there. The 1 is used to make sure that a change was made
in one cell only. If you select several cells, as D1:D4 or C1:H6 and hit the
[Del] key, then this routine would see 'Target' as that range, and would know
that 4 or 6 cells had been changed. This line verifies that one cell only in
column D was changed in value.

#2) You could record macros while setting color in cells and look at the
code generated, or you can visit this page by Dave Richie that has the colors
for fonts and cell backgrounds all mapped out (scroll down the page a bit to
see them)
http://www.mvps.org/dmcritchie/excel/colors.htm

#3) that actually meant that if I did not check for less than zero, then
negative numbers would have also been set to green. With the color palette
available to you now can set variable selectedColor = 15 (or 16 or 48)
depending on the shade of gray you want.

#4) Correct - Target.Row will refer to the row number of the changed cell in
column D. With the need to now test columns S and D, that line of code is
going to change anyhow.

#5) That got covered in #1 above.

Here is revised code that:
works on values in columns D/S separately
sets negative values to a shade of gray
does not change colors of rows above row 5
allows you to also define a lower boundary row for your table

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyRange As Range
Dim selectedColor As Integer

'column D is 4th column, S is 19th column
If Target.Column < 4 And Target.Column < 19 _
Or Target.Cells.Count 1 Then
Exit Sub ' not in columns D or S, or multiple cells chosen
End If
'this will prevent color changes if cell changed
'is above or below area of your table
'with Rows.Count it goes to end of sheet
'if you change Rows.Count to a number it will
'use that number to exclude rows below your table
If Target.Row < 5 Or Target.Row Rows.Count Then
Exit Sub
End If
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target
Case Is < 0
'negative #s gray
selectedColor = 15 ' 10% gray
Case Is <= 10
selectedColor = 4 ' Green
Case Is <= 20
selectedColor = 41 ' Blue
Case Is <= 30
selectedColor = 3 ' Red
Case Is <= 40
selectedColor = 6 ' Yellow
Case Is <= 50
selectedColor = 45 ' Orange
Case Else
selectedColor = xlNone '
End Select
End If
If Target.Column = 4 Then
' in column D
Set anyRange = Range("B" & Target.Row & ":E" & Target.Row)
Else
'must be in column S
Set anyRange = Range("Q" & Target.Row & ":U" & Target.Row)
End If
anyRange.Interior.ColorIndex = selectedColor
End Sub

You can download a working version of this from:
http://www.jlathamsite.com/uploads/ColorsForGunjani.xls


"Gunjani" wrote:

On 18 Feb, 01:29, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
You're headed in the right direction. As you said, Right-click on Team8 tab
and select view code, then paste this into it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyRange As Range
Dim selectedColor As Integer

If Target.Column < 4 Or Target.Cells.Count 1 Then
Exit Sub ' not in column D or multiple cells chosen (as in deleted)
End If
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target
Case Is < 0
'otherwise negative #s are green
selectedColor = xlNone
Case Is <= 10
selectedColor = 4 ' Green
Case Is <= 20
selectedColor = 41 ' Blue
Case Is <= 30
selectedColor = 3 ' Red
Case Is <= 40
selectedColor = 6 ' Yellow
Case Is <= 50
selectedColor = 45 ' Orange
Case Else
selectedColor = xlNone '
End Select
End If
Set anyRange = Union(Range("B" & Target.Row & ":E" & Target.Row), _
Range("Q" & Target.Row & ":U" & Target.Row))
anyRange.Interior.ColorIndex = selectedColor
End Sub

You didn't say what to do if values were less than zero or greater than 50,
so I've set things up that in those instances, previous coloring is removed.



"Gunjani" wrote:
Hi

I would like to create the following....

In Column D if cell value is between 0 and 10 then its row in column
B, C, D, E and F should be green;
In Column D if cell value is between 11 and 20 then column B, C, D, E
and F of the same row should be blue;
In Column D if cell value is between 21 and 30 then column B, C, D, E
and F of the same row should be red;
In Column D if cell value is between 31 and 40 then column B, C, D, E
and F of the same row should be yellow;
In Column D if cell value is between 41 and 50 then column B, C, D, E
and F of the same row should be orange;
etc

The same rule to apply for Columns Q, R,S, T and U

The worksheet is called 'Team 8'

I'm assuming this can only be performed in Macros so how do I begin?
Right click on Team 8 worksheet tab select 'View Code' and then I'm
unsure what to enter after that?

Can some one assist

Gunjani- Hide quoted text -

- Show quoted text -

Thank you but I cannot get it working!!

Once it is pasted into the worksheet how do i run it, do I just save
the worksheet?

Further more I misinformed you, I wish the same rules to apply to
column Q, R, S, T and U based on the values in COLUMN S.

For future amendements can u explain

1) What does this line refer to 'If Target.Column < 4 Or
Target.Cells.Count 1 ' i.e is target column set at 4 becoz it s
reference is column D? and target cell count1 becoz it starts from
cell 1 (if so my table starts from Row 5)

2) If I wish to change the colour codes, where may I get the Colour
refernce no, i.e 4 (green), 41 ( blue)

3) Case Is < 0
'otherwise negative #s are green
Does this mean negative numbers will change to green?, if so I like to
change it to grey

4) I assume the following is based on value of Column D...
Set anyRange = Union(Range("B" & Target.Row & ":E" & Target.Row), _
Range("Q" & Target.Row & ":U" & Target.Row))
anyRange.Interior.ColorIndex = selectedColor

I wish the colours in to change in columns B to F to be determined by
the value in Column D, and the colours in columns Q to U to be
determined by Column S values.

5) What amendments would I need to make if, later, I wish to rearrange
the table so that columns are shifted to the right by say 2 columns
i.e values in Column D is in column F etc.

Thank you for ur assistance

On 18 Feb, 14:53, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
If you have placed the code into the worksheet's code area and macros are
enabled, then it should automatically run when you type a value from 0
through 50 into any cell in column D.

Your questions:
#1) Yes, the 4 is there because column D is the 4th column on a sheet. If
you moved things two columns right (using F as the test column) then 6 would
be the value to use there. The 1 is used to make sure that a change was made
in one cell only. If you select several cells, as D1:D4 or C1:H6 and hit the
[Del] key, then this routine would see 'Target' as that range, and would know
that 4 or 6 cells had been changed. This line verifies that one cell only in
column D was changed in value.

#2) You could record macros while setting color in cells and look at the
code generated, or you can visit this page by Dave Richie that has the colors
for fonts and cell backgrounds all mapped out (scroll down the page a bit to
see them)http://www.mvps.org/dmcritchie/excel/colors.htm

#3) that actually meant that if I did not check for less than zero, then
negative numbers would have also been set to green. With the color palette
available to you now can set variable selectedColor = 15 (or 16 or 48)
depending on the shade of gray you want.

#4) Correct - Target.Row will refer to the row number of the changed cell in
column D. With the need to now test columns S and D, that line of code is
going to change anyhow.

#5) That got covered in #1 above.

Here is revised code that:
works on values in columns D/S separately
sets negative values to a shade of gray
does not change colors of rows above row 5
allows you to also define a lower boundary row for your table

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyRange As Range
Dim selectedColor As Integer

'column D is 4th column, S is 19th column
If Target.Column < 4 And Target.Column < 19 _
Or Target.Cells.Count 1 Then
Exit Sub ' not in columns D or S, or multiple cells chosen
End If
'this will prevent color changes if cell changed
'is above or below area of your table
'with Rows.Count it goes to end of sheet
'if you change Rows.Count to a number it will
'use that number to exclude rows below your table
If Target.Row < 5 Or Target.Row Rows.Count Then
Exit Sub
End If
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target
Case Is < 0
'negative #s gray
selectedColor = 15 ' 10% gray
Case Is <= 10
selectedColor = 4 ' Green
Case Is <= 20
selectedColor = 41 ' Blue
Case Is <= 30
selectedColor = 3 ' Red
Case Is <= 40
selectedColor = 6 ' Yellow
Case Is <= 50
selectedColor = 45 ' Orange
Case Else
selectedColor = xlNone '
End Select
End If
If Target.Column = 4 Then
' in column D
Set anyRange = Range("B" & Target.Row & ":E" & Target.Row)
Else
'must be in column S
Set anyRange = Range("Q" & Target.Row & ":U" & Target.Row)
End If
anyRange.Interior.ColorIndex = selectedColor
End Sub

You can download a working version of this from:http://www.jlathamsite.com/uploads/ColorsForGunjani.xls



"Gunjani" wrote:
On 18 Feb, 01:29, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
You're headed in the right direction. As you said, Right-click on Team8 tab
and select view code, then paste this into it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyRange As Range
Dim selectedColor As Integer

If Target.Column < 4 Or Target.Cells.Count 1 Then
Exit Sub ' not in column D or multiple cells chosen (as in deleted)
End If
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target
Case Is < 0
'otherwise negative #s are green
selectedColor = xlNone
Case Is <= 10
selectedColor = 4 ' Green
Case Is <= 20
selectedColor = 41 ' Blue
Case Is <= 30
selectedColor = 3 ' Red
Case Is <= 40
selectedColor = 6 ' Yellow
Case Is <= 50
selectedColor = 45 ' Orange
Case Else
selectedColor = xlNone '
End Select
End If
Set anyRange = Union(Range("B" & Target.Row & ":E" & Target.Row), _
Range("Q" & Target.Row & ":U" & Target.Row))
anyRange.Interior.ColorIndex = selectedColor
End Sub

You didn't say what to do if values were less than zero or greater than 50,
so I've set things up that in those instances, previous coloring is removed.

"Gunjani" wrote:
Hi

I would like to create the following....

In Column D if cell value is between 0 and 10 then its row in column
B, C, D, E and F should be green;
In Column D if cell value is between 11 and 20 then column B, C, D, E
and F of the same row should be blue;
In Column D if cell value is between 21 and 30 then column B, C, D, E
and F of the same row should be red;
In Column D if cell value is between 31 and 40 then column B, C, D, E
and F of the same row should be yellow;
In Column D if cell value is between 41 and 50 then column B, C, D, E
and F of the same row should be orange;
etc

The same rule to apply for Columns Q, R,S, T and U

The worksheet is called 'Team 8'

I'm assuming this can only be performed in Macros so how do I begin?
Right click on Team 8 worksheet tab select 'View Code' and then I'm
unsure what to enter after that?

Can some one assist

Gunjani- Hide quoted text -

- Show quoted text -

Thank you but I cannot get it working!!

Once it is pasted into the worksheet how do i run it, do I just save
the worksheet?

Further more I misinformed you, I wish the same rules to apply to
column Q, R, S, T and U based on the values in COLUMN S.

For future amendements can u explain

1) What does this line refer to 'If Target.Column < 4 Or
Target.Cells.Count 1 ' i.e is target column set at 4 becoz it s
reference is column D? and target cell count1 becoz it starts from
cell 1 (if so my table starts from Row 5)

2) If I wish to change the colour codes, where may I get the Colour
refernce no, i.e 4 (green), 41 ( blue)

3) Case Is < 0
'otherwise negative #s are green
Does this mean negative numbers will change to green?, if so I like to
change it to grey

4) I assume the following is based on value of Column D...
Set anyRange = Union(Range("B" & Target.Row & ":E" & Target.Row), _
Range("Q" & Target.Row & ":U" & Target.Row))
anyRange.Interior.ColorIndex = selectedColor

I wish the colours in to change in columns B to F to be determined by
the value in Column D, and the colours in columns Q to U to be
determined by Column S values.

5) What amendments would I need to make if, later, I wish to rearrange
the table so that columns are shifted to the right by say 2 columns
i.e values in Column D is in column F etc.

Thank you for ur assistance- Hide quoted text -

- Show quoted text -

Hi J,
Thanks for the example it assisted in what was going. In my case the
macro works but not as exactly as I want it.
The Macro works ONLY on entering values in Column D and Column S, but
what if there is pre-existing values in Column D and S (as result a
formula and dynamically changes).

My Table consists of a name list in cell B1, when a different name is
selected in B1, the values in Column D and S also change respectively.
So how can this macro be modified to change dynamically with the
formula. Principally I do not enter any values they appear
automatically as cell B1 changes. The above macro rewuires me to enter
data in columns D & S manually.

This is the formula for Column D=IF($A5="Saturday",VLOOKUP($C5,'Sat
2007 Duty Schedule'!$A:$T,7,0),IF($A5="Sunday",VLOOKUP($C5,' Sun 2007
Duty Schedule'!$A:$T,7,0),VLOOKUP($C5,'MF 2007 Duty Schedule'!$A:$T,
7,0)))

This is the formula for Column S:=IF($A5="Saturday",VLOOKUP($C5,'Sat
2007 Duty Schedule'!$A:$T,13,0),IF($A5="Sunday",VLOOKUP($C5, 'Sun 2007
Duty Schedule'!$A:$T,13,0),VLOOKUP($C5,'MF 2007 Duty Schedule'!$A:$T,
13,0)))

Thanks
Regards

Gunj

If you insist on bottom posting, you should at least learn how to <snip!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gunjani" wrote in message
oups.com...
On 18 Feb, 14:53, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
If you have placed the code into the worksheet's code area and macros
are
enabled, then it should automatically run when you type a value from 0
through 50 into any cell in column D.

Your questions:
#1) Yes, the 4 is there because column D is the 4th column on a sheet.
If
you moved things two columns right (using F as the test column) then 6
would
be the value to use there. The 1 is used to make sure that a change was
made
in one cell only. If you select several cells, as D1:D4 or C1:H6 and
hit the
[Del] key, then this routine would see 'Target' as that range, and would
know
that 4 or 6 cells had been changed. This line verifies that one cell
only in
column D was changed in value.

#2) You could record macros while setting color in cells and look at the
code generated, or you can visit this page by Dave Richie that has the
colors
for fonts and cell backgrounds all mapped out (scroll down the page a
bit to
see them)http://www.mvps.org/dmcritchie/excel/colors.htm

#3) that actually meant that if I did not check for less than zero, then
negative numbers would have also been set to green. With the color
palette
available to you now can set variable selectedColor = 15 (or 16 or 48)
depending on the shade of gray you want.

#4) Correct - Target.Row will refer to the row number of the changed
cell in
column D. With the need to now test columns S and D, that line of code
is
going to change anyhow.

#5) That got covered in #1 above.

Here is revised code that:
works on values in columns D/S separately
sets negative values to a shade of gray
does not change colors of rows above row 5
allows you to also define a lower boundary row for your table

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyRange As Range
Dim selectedColor As Integer

'column D is 4th column, S is 19th column
If Target.Column < 4 And Target.Column < 19 _
Or Target.Cells.Count 1 Then
Exit Sub ' not in columns D or S, or multiple cells chosen
End If
'this will prevent color changes if cell changed
'is above or below area of your table
'with Rows.Count it goes to end of sheet
'if you change Rows.Count to a number it will
'use that number to exclude rows below your table
If Target.Row < 5 Or Target.Row Rows.Count Then
Exit Sub
End If
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target
Case Is < 0
'negative #s gray
selectedColor = 15 ' 10% gray
Case Is <= 10
selectedColor = 4 ' Green
Case Is <= 20
selectedColor = 41 ' Blue
Case Is <= 30
selectedColor = 3 ' Red
Case Is <= 40
selectedColor = 6 ' Yellow
Case Is <= 50
selectedColor = 45 ' Orange
Case Else
selectedColor = xlNone '
End Select
End If
If Target.Column = 4 Then
' in column D
Set anyRange = Range("B" & Target.Row & ":E" & Target.Row)
Else
'must be in column S
Set anyRange = Range("Q" & Target.Row & ":U" & Target.Row)
End If
anyRange.Interior.ColorIndex = selectedColor
End Sub

You can download a working version of this
from:http://www.jlathamsite.com/uploads/ColorsForGunjani.xls



"Gunjani" wrote:
On 18 Feb, 01:29, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
You're headed in the right direction. As you said, Right-click on
Team8 tab
and select view code, then paste this into it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyRange As Range
Dim selectedColor As Integer

If Target.Column < 4 Or Target.Cells.Count 1 Then
Exit Sub ' not in column D or multiple cells chosen (as in
deleted)
End If
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target
Case Is < 0
'otherwise negative #s are green
selectedColor = xlNone
Case Is <= 10
selectedColor = 4 ' Green
Case Is <= 20
selectedColor = 41 ' Blue
Case Is <= 30
selectedColor = 3 ' Red
Case Is <= 40
selectedColor = 6 ' Yellow
Case Is <= 50
selectedColor = 45 ' Orange
Case Else
selectedColor = xlNone '
End Select
End If
Set anyRange = Union(Range("B" & Target.Row & ":E" & Target.Row), _
Range("Q" & Target.Row & ":U" & Target.Row))
anyRange.Interior.ColorIndex = selectedColor
End Sub

You didn't say what to do if values were less than zero or greater
than 50,
so I've set things up that in those instances, previous coloring is
removed.

"Gunjani" wrote:
Hi

I would like to create the following....

In Column D if cell value is between 0 and 10 then its row in
column
B, C, D, E and F should be green;
In Column D if cell value is between 11 and 20 then column B, C,
D, E
and F of the same row should be blue;
In Column D if cell value is between 21 and 30 then column B, C,
D, E
and F of the same row should be red;
In Column D if cell value is between 31 and 40 then column B, C,
D, E
and F of the same row should be yellow;
In Column D if cell value is between 41 and 50 then column B, C,
D, E
and F of the same row should be orange;
etc

The same rule to apply for Columns Q, R,S, T and U

The worksheet is called 'Team 8'

I'm assuming this can only be performed in Macros so how do I
begin?
Right click on Team 8 worksheet tab select 'View Code' and then
I'm
unsure what to enter after that?

Can some one assist

Gunjani- Hide quoted text -

- Show quoted text -

Thank you but I cannot get it working!!

Once it is pasted into the worksheet how do i run it, do I just save
the worksheet?

Further more I misinformed you, I wish the same rules to apply to
column Q, R, S, T and U based on the values in COLUMN S.

For future amendements can u explain

1) What does this line refer to 'If Target.Column < 4 Or
Target.Cells.Count 1 ' i.e is target column set at 4 becoz it s
reference is column D? and target cell count1 becoz it starts from
cell 1 (if so my table starts from Row 5)

2) If I wish to change the colour codes, where may I get the Colour
refernce no, i.e 4 (green), 41 ( blue)

3) Case Is < 0
'otherwise negative #s are green
Does this mean negative numbers will change to green?, if so I like to
change it to grey

4) I assume the following is based on value of Column D...
Set anyRange = Union(Range("B" & Target.Row & ":E" & Target.Row), _
Range("Q" & Target.Row & ":U" & Target.Row))
anyRange.Interior.ColorIndex = selectedColor

I wish the colours in to change in columns B to F to be determined by
the value in Column D, and the colours in columns Q to U to be
determined by Column S values.

5) What amendments would I need to make if, later, I wish to rearrange
the table so that columns are shifted to the right by say 2 columns
i.e values in Column D is in column F etc.

Thank you for ur assistance- Hide quoted text -

- Show quoted text -

Hi J,
Thanks for the example it assisted in what was going. In my case the
macro works but not as exactly as I want it.
The Macro works ONLY on entering values in Column D and Column S, but
what if there is pre-existing values in Column D and S (as result a
formula and dynamically changes).

My Table consists of a name list in cell B1, when a different name is
selected in B1, the values in Column D and S also change respectively.
So how can this macro be modified to change dynamically with the
formula. Principally I do not enter any values they appear
automatically as cell B1 changes. The above macro rewuires me to enter
data in columns D & S manually.

This is the formula for Column D=IF($A5="Saturday",VLOOKUP($C5,'Sat
2007 Duty Schedule'!$A:$T,7,0),IF($A5="Sunday",VLOOKUP($C5,' Sun 2007
Duty Schedule'!$A:$T,7,0),VLOOKUP($C5,'MF 2007 Duty Schedule'!$A:$T,
7,0)))

This is the formula for Column S:=IF($A5="Saturday",VLOOKUP($C5,'Sat
2007 Duty Schedule'!$A:$T,13,0),IF($A5="Sunday",VLOOKUP($C5, 'Sun 2007
Duty Schedule'!$A:$T,13,0),VLOOKUP($C5,'MF 2007 Duty Schedule'!$A:$T,
13,0)))

Thanks
Regards

Gunj

Changes to cell values through worksheet functions do not trigger the
_Change() event. So now we need to go look and see when you make a change in
column B and when you do that, then we have to examine the value in D and S
to see if any color changes are needed. This code does that. It also
automatically chooses which value in which column to look at using the code
you'll see commented on in there referring to cells D1 and S1 if you move
things around on your sheet and those end up in different columns, just put
in the new column identification letters and it will still work properly.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyRange As Range
Dim selectedColor As Integer

'column B is 2nd column
If Target.Column < 2 Or Target.Cells.Count 1 Then
Exit Sub ' not in column B
End If
'this will prevent color changes if cell changed
'is above or below area of your table
'with Rows.Count it goes to end of sheet
'if you change Rows.Count to a number it will
'use that number to exclude rows below your table
If Target.Row < 5 Or Target.Row Rows.Count Then
Exit Sub
End If
'first we deal with the B through E group
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
'we could use any cell in column D
'to get column D's column number
'so I just chose D1, same below
'where I use S1 to get column offset to
'column S from whatever column our target is in.
Select Case Target.Offset(0, Range("D1").Column - Target.Column).Value
Case Is < 0
'negative #s gray
selectedColor = 15 ' 10% gray
Case Is <= 10
selectedColor = 4 ' Green
Case Is <= 20
selectedColor = 41 ' Blue
Case Is <= 30
selectedColor = 3 ' Red
Case Is <= 40
selectedColor = 6 ' Yellow
Case Is <= 50
selectedColor = 45 ' Orange
Case Else
selectedColor = xlNone '
End Select
End If
Set anyRange = Range("B" & Target.Row & ":E" & Target.Row)
anyRange.Interior.ColorIndex = selectedColor

'next we deal with the Q through U group
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target.Offset(0, Range("S1").Column - Target.Column).Value
Case Is < 0
'negative #s gray
selectedColor = 15 ' 10% gray
Case Is <= 10
selectedColor = 4 ' Green
Case Is <= 20
selectedColor = 41 ' Blue
Case Is <= 30
selectedColor = 3 ' Red
Case Is <= 40
selectedColor = 6 ' Yellow
Case Is <= 50
selectedColor = 45 ' Orange
Case Else
selectedColor = xlNone '
End Select
End If
Set anyRange = Range("Q" & Target.Row & ":U" & Target.Row)
anyRange.Interior.ColorIndex = selectedColor

End Sub


"Gunjani" wrote:

On 18 Feb, 14:53, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
If you have placed the code into the worksheet's code area and macros are
enabled, then it should automatically run when you type a value from 0
through 50 into any cell in column D.

Your questions:
#1) Yes, the 4 is there because column D is the 4th column on a sheet. If
you moved things two columns right (using F as the test column) then 6 would
be the value to use there. The 1 is used to make sure that a change was made
in one cell only. If you select several cells, as D1:D4 or C1:H6 and hit the
[Del] key, then this routine would see 'Target' as that range, and would know
that 4 or 6 cells had been changed. This line verifies that one cell only in
column D was changed in value.

#2) You could record macros while setting color in cells and look at the
code generated, or you can visit this page by Dave Richie that has the colors
for fonts and cell backgrounds all mapped out (scroll down the page a bit to
see them)http://www.mvps.org/dmcritchie/excel/colors.htm

#3) that actually meant that if I did not check for less than zero, then
negative numbers would have also been set to green. With the color palette
available to you now can set variable selectedColor = 15 (or 16 or 48)
depending on the shade of gray you want.

#4) Correct - Target.Row will refer to the row number of the changed cell in
column D. With the need to now test columns S and D, that line of code is
going to change anyhow.

#5) That got covered in #1 above.

Here is revised code that:
works on values in columns D/S separately
sets negative values to a shade of gray
does not change colors of rows above row 5
allows you to also define a lower boundary row for your table

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyRange As Range
Dim selectedColor As Integer

'column D is 4th column, S is 19th column
If Target.Column < 4 And Target.Column < 19 _
Or Target.Cells.Count 1 Then
Exit Sub ' not in columns D or S, or multiple cells chosen
End If
'this will prevent color changes if cell changed
'is above or below area of your table
'with Rows.Count it goes to end of sheet
'if you change Rows.Count to a number it will
'use that number to exclude rows below your table
If Target.Row < 5 Or Target.Row Rows.Count Then
Exit Sub
End If
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target
Case Is < 0
'negative #s gray
selectedColor = 15 ' 10% gray
Case Is <= 10
selectedColor = 4 ' Green
Case Is <= 20
selectedColor = 41 ' Blue
Case Is <= 30
selectedColor = 3 ' Red
Case Is <= 40
selectedColor = 6 ' Yellow
Case Is <= 50
selectedColor = 45 ' Orange
Case Else
selectedColor = xlNone '
End Select
End If
If Target.Column = 4 Then
' in column D
Set anyRange = Range("B" & Target.Row & ":E" & Target.Row)
Else
'must be in column S
Set anyRange = Range("Q" & Target.Row & ":U" & Target.Row)
End If
anyRange.Interior.ColorIndex = selectedColor
End Sub

You can download a working version of this from:http://www.jlathamsite.com/uploads/ColorsForGunjani.xls



"Gunjani" wrote:
On 18 Feb, 01:29, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
You're headed in the right direction. As you said, Right-click on Team8 tab
and select view code, then paste this into it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyRange As Range
Dim selectedColor As Integer

If Target.Column < 4 Or Target.Cells.Count 1 Then
Exit Sub ' not in column D or multiple cells chosen (as in deleted)
End If
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target
Case Is < 0
'otherwise negative #s are green
selectedColor = xlNone
Case Is <= 10
selectedColor = 4 ' Green
Case Is <= 20
selectedColor = 41 ' Blue
Case Is <= 30
selectedColor = 3 ' Red
Case Is <= 40
selectedColor = 6 ' Yellow
Case Is <= 50
selectedColor = 45 ' Orange
Case Else
selectedColor = xlNone '
End Select
End If
Set anyRange = Union(Range("B" & Target.Row & ":E" & Target.Row), _
Range("Q" & Target.Row & ":U" & Target.Row))
anyRange.Interior.ColorIndex = selectedColor
End Sub

You didn't say what to do if values were less than zero or greater than 50,
so I've set things up that in those instances, previous coloring is removed.

"Gunjani" wrote:
Hi

I would like to create the following....

In Column D if cell value is between 0 and 10 then its row in column
B, C, D, E and F should be green;
In Column D if cell value is between 11 and 20 then column B, C, D, E
and F of the same row should be blue;
In Column D if cell value is between 21 and 30 then column B, C, D, E
and F of the same row should be red;
In Column D if cell value is between 31 and 40 then column B, C, D, E
and F of the same row should be yellow;
In Column D if cell value is between 41 and 50 then column B, C, D, E
and F of the same row should be orange;
etc

The same rule to apply for Columns Q, R,S, T and U

The worksheet is called 'Team 8'

I'm assuming this can only be performed in Macros so how do I begin?
Right click on Team 8 worksheet tab select 'View Code' and then I'm
unsure what to enter after that?

Can some one assist

Gunjani- Hide quoted text -

- Show quoted text -

Thank you but I cannot get it working!!

Once it is pasted into the worksheet how do i run it, do I just save
the worksheet?

Further more I misinformed you, I wish the same rules to apply to
column Q, R, S, T and U based on the values in COLUMN S.

For future amendements can u explain

1) What does this line refer to 'If Target.Column < 4 Or
Target.Cells.Count 1 ' i.e is target column set at 4 becoz it s
reference is column D? and target cell count1 becoz it starts from
cell 1 (if so my table starts from Row 5)

2) If I wish to change the colour codes, where may I get the Colour
refernce no, i.e 4 (green), 41 ( blue)

3) Case Is < 0
'otherwise negative #s are green
Does this mean negative numbers will change to green?, if so I like to
change it to grey

4) I assume the following is based on value of Column D...
Set anyRange = Union(Range("B" & Target.Row & ":E" & Target.Row), _
Range("Q" & Target.Row & ":U" & Target.Row))
anyRange.Interior.ColorIndex = selectedColor

I wish the colours in to change in columns B to F to be determined by
the value in Column D, and the colours in columns Q to U to be
determined by Column S values.

5) What amendments would I need to make if, later, I wish to rearrange
the table so that columns are shifted to the right by say 2 columns
i.e values in Column D is in column F etc.

Thank you for ur assistance- Hide quoted text -

- Show quoted text -

Hi J,
Thanks for the example it assisted in what was going. In my case the
macro works but not as exactly as I want it.
The Macro works ONLY on entering values in Column D and Column S, but
what if there is pre-existing values in Column D and S (as result a
formula and dynamically changes).

My Table consists of a name list in cell B1, when a different name is
selected in B1, the values in Column D and S also change respectively.
So how can this macro be modified to change dynamically with the
formula. Principally I do not enter any values they appear
automatically as cell B1 changes. The above macro rewuires me to enter
data in columns D & S manually.

This is the formula for Column D=IF($A5="Saturday",VLOOKUP($C5,'Sat
2007 Duty Schedule'!$A:$T,7,0),IF($A5="Sunday",VLOOKUP($C5,' Sun 2007
Duty Schedule'!$A:$T,7,0),VLOOKUP($C5,'MF 2007 Duty Schedule'!$A:$T,
7,0)))

This is the formula for Column S:=IF($A5="Saturday",VLOOKUP($C5,'Sat
2007 Duty Schedule'!$A:$T,13,0),IF($A5="Sunday",VLOOKUP($C5, 'Sun 2007
Duty Schedule'!$A:$T,13,0),VLOOKUP($C5,'MF 2007 Duty Schedule'!$A:$T,
13,0)))

Thanks
Regards

Gunj