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