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

Sorry for the delayed reply just returned from hols...

I've tried to use the macro but still no joy I made a few amendments,
but just to recap this is what I wish to perfrom

1. A Name is selected from a drop-down menu in Cell B1
2. Based on the Name, data is retrieved onto the spreadsheet in Column
C thru to Column V
3. Cells I and O contains data which are numbers ranging from 0 to
100, or words like 'spare', 'washing', the data changes dynamically
when the a different name is selectedin B1.
4. I wish rows in columns G thru to K (originally B to E in the
previous example) to be colour coded with respective data in cell I as
per previously explained
5.I wish rows in columns M thru to q (originally Q to U in the
previous example) to be colour coded with respective data in cell O as
previously explained.

6. Below is my attempted revision on your creation but it does not
work... where am I going wrong?


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 G through K group
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
'we could use any cell in column I
'to get column I's column number
'so I just chose I1, same below
'where I use O1 to get column offset to
'column O from whatever column our target is in.
Select Case Target.Offset(0, Range("I1").Column -
Target.Column).Value
Case Is < 0
'negative #s gray
selectedColor = 15 ' 10% gray
Case Is <= 29
selectedColor = 6 ' Yellow
Case Is <= 49
selectedColor = 4 ' Green
Case Is <= 69
selectedColor = 41 ' Blue
Case Is <= 79
selectedColor = 13 ' Mauve
Case Is <= SPARE
selectedColor = 16 ' Grey
Case Is <= Washing
selectedColor = 10 ' Dark Green
Case Else
selectedColor = xlNone '
End Select
End If
Set anyRange = Range("G" & Target.Row & ":K" & Target.Row)
anyRange.Interior.ColorIndex = selectedColor

'next we deal with the M through Q group
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target.Offset(0, Range("O1").Column -
Target.Column).Value
Case Is < 0
'negative #s gray
selectedColor = 15 ' 10% gray
Case Is <= 29
selectedColor = 6 ' Yellow
Case Is <= 49
selectedColor = 4 ' Green
Case Is <= 69
selectedColor = 41 ' Blue
Case Is <= 79
selectedColor = 13 ' Mauve
Case Is <= SPARE
selectedColor = 16 ' Grey
Case Is <= Washing
selectedColor = 10 ' Dark Green
Case Else
selectedColor = xlNone '
End Select
End If
Set anyRange = Range("M" & Target.Row & ":Q" & Target.Row)
anyRange.Interior.ColorIndex = selectedColor
End Sub

I will sit down with your new definition of requirements, seems suddenly we
have words to possibly deal with along with numbers?, and will get back to
you on this. At least it still looks as if we need to trigger on a change in
B.

The problems were created by having both words and numbers in your test
columns, I and O. When you try to evaluate a word as a number, then the
result is usually a headache.

I've modified the code to look specifically for either a word or a number in
the cells and set up two different Select Case constructs for each condition.
That logic is used for both tests of values in column I and in column O.

Also, if you were not getting an error in the lines that read like
Case Is <= SPARE
it is because you did not use Option Explicit in your code module and VB was
trying to treat SPARE and Washing as variables/constants, but since they had
not been defined with a DIM statement (which Option Explicit would have
required) they evaluate to zero. When testing text/words with a string that
is that text/word(s) you must enclose it in double quotes as
Case Is = "SPARE"
Remember that VB is case sensitive, so "SPARE" and "Spare" are two different
values - that's why the code converts words from the worksheet to all
UPPERCASE and removes any leading/trailing blanks before making the tests -
better control of the testing and the results.

Here's the new code. Watch out for line breaks forced by the forum here. I
have sent you a working version of this in a test workbook via eMail.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyRange As Range
Dim selectedColor As Integer
Dim testValue As Variant ' can be words or number

'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 G through K group based on value in I
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
'we now need to know if we are dealing with a number or a word
If IsNumeric(Target.Offset(0, Range("I1").Column - Target.Column).Value)
Then
'test your numbers in here
Select Case Target.Offset(0, Range("I1").Column - Target.Column).Value
Case Is < 0
'negative #s gray
selectedColor = 15 ' 10% gray
Case Is <= 29
selectedColor = 6 ' Yellow
Case Is <= 49
selectedColor = 4 ' Green
Case Is <= 69
selectedColor = 41 ' Blue
Case Is <= 79
selectedColor = 13 ' Mauve
Case Else
selectedColor = xlNone '
End Select

Else
testValue = UCase(Trim(Target.Offset(0, Range("I1").Column -
Target.Column).Value))
'test your words here, must be entered in UPPERCASE without
leading/trailing spaces
' they do not have to be uppercase on the worksheet - the line above
turns what ever
' is in the worksheet cell to all UPPERCASE and removes any leading
or trailing
' spaces may be in it. This makes for more certain comparisons.
'also test for = in these, not <= or =
Select Case testValue
Case Is = "SPARE" ' must be all uppercase in this test now
selectedColor = 16 ' Grey
Case Is = "WASHING"
selectedColor = 10 ' Dark Green
Case Else
selectedColor = xlNone '
End Select

End If
End If
Set anyRange = Range("G" & Target.Row & ":K" & Target.Row)
anyRange.Interior.ColorIndex = selectedColor

'next we deal with the M through Q group based on value in O
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
'we now need to know if we are dealing with a number or a word
If IsNumeric(Target.Offset(0, Range("O1").Column - Target.Column).Value)
Then
'test your numbers in here
Select Case Target.Offset(0, Range("O1").Column - Target.Column).Value
Case Is < 0
'negative #s gray
selectedColor = 15 ' 10% gray
Case Is <= 29
selectedColor = 6 ' Yellow
Case Is <= 49
selectedColor = 4 ' Green
Case Is <= 69
selectedColor = 41 ' Blue
Case Is <= 79
selectedColor = 13 ' Mauve
Case Else
selectedColor = xlNone '
End Select

Else
testValue = UCase(Trim(Target.Offset(0, Range("O1").Column -
Target.Column).Value))
'test your words here, must be entered in UPPERCASE without
leading/trailing spaces
' they do not have to be uppercase on the worksheet - the line above
turns what ever
' is in the worksheet cell to all UPPERCASE and removes any leading
or trailing
' spaces may be in it. This makes for more certain comparisons.
'Test for = in these, not <= or =
Select Case testValue
Case Is = "SPARE" ' must be all uppercase in this test now
selectedColor = 16 ' Grey
Case Is = "WASHING"
selectedColor = 10 ' Dark Green
Case Else
selectedColor = xlNone '
End Select

End If
End If
Set anyRange = Range("M" & Target.Row & ":Q" & Target.Row)
anyRange.Interior.ColorIndex = selectedColor

End Sub


"Gunjani" wrote:

Sorry for the delayed reply just returned from hols...

I've tried to use the macro but still no joy I made a few amendments,
but just to recap this is what I wish to perfrom

1. A Name is selected from a drop-down menu in Cell B1
2. Based on the Name, data is retrieved onto the spreadsheet in Column
C thru to Column V
3. Cells I and O contains data which are numbers ranging from 0 to
100, or words like 'spare', 'washing', the data changes dynamically
when the a different name is selectedin B1.
4. I wish rows in columns G thru to K (originally B to E in the
previous example) to be colour coded with respective data in cell I as
per previously explained
5.I wish rows in columns M thru to q (originally Q to U in the
previous example) to be colour coded with respective data in cell O as
previously explained.

6. Below is my attempted revision on your creation but it does not
work... where am I going wrong?


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 G through K group
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
'we could use any cell in column I
'to get column I's column number
'so I just chose I1, same below
'where I use O1 to get column offset to
'column O from whatever column our target is in.
Select Case Target.Offset(0, Range("I1").Column -
Target.Column).Value
Case Is < 0
'negative #s gray
selectedColor = 15 ' 10% gray
Case Is <= 29
selectedColor = 6 ' Yellow
Case Is <= 49
selectedColor = 4 ' Green
Case Is <= 69
selectedColor = 41 ' Blue
Case Is <= 79
selectedColor = 13 ' Mauve
Case Is <= SPARE
selectedColor = 16 ' Grey
Case Is <= Washing
selectedColor = 10 ' Dark Green
Case Else
selectedColor = xlNone '
End Select
End If
Set anyRange = Range("G" & Target.Row & ":K" & Target.Row)
anyRange.Interior.ColorIndex = selectedColor

'next we deal with the M through Q group
If IsEmpty(Target) Then
'reset if contents deleted
selectedColor = xlNone
Else
Select Case Target.Offset(0, Range("O1").Column -
Target.Column).Value
Case Is < 0
'negative #s gray
selectedColor = 15 ' 10% gray
Case Is <= 29
selectedColor = 6 ' Yellow
Case Is <= 49
selectedColor = 4 ' Green
Case Is <= 69
selectedColor = 41 ' Blue
Case Is <= 79
selectedColor = 13 ' Mauve
Case Is <= SPARE
selectedColor = 16 ' Grey
Case Is <= Washing
selectedColor = 10 ' Dark Green
Case Else
selectedColor = xlNone '
End Select
End If
Set anyRange = Range("M" & Target.Row & ":Q" & Target.Row)
anyRange.Interior.ColorIndex = selectedColor
End Sub