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