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
4 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
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 count>1 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), _
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 -
