Hi,
I've written some code that alters the interior of a cell colour based on
certain criteria. The code works fine in Excel 2003 but in Excel 97 I
sometimes get runtime error 1004 relating to the Interior class. I do need
it to work in excel 97.

I run the Excel 97 on a laptop running Windows 98, and Excel 2003 on my
desktop running win xp pro service pack 2.
The problem code is as follows:

Sub AgeCriteriaChecker()
Dim CellsToBeChecked As Range 'this is the range that contains the ages
Set CellsToBeChecked = Range(Range("C2"), Range("C65536").End(xlUp))
Dim i , Age As Integer

For i = 1 To CellsToBeChecked.Cells.Count
Age = CellsToBeChecked.Cells(i).Value

Select Case Age
Case 20 To 34
CellsToBeChecked.Cells(i, 2).Interior.Color = vbRed
CellsToBeChecked.Cells(i, 2).Font.ColorIndex = 2

Case 35 To 49
CellsToBeChecked.Cells(i, 2).Interior.Color = vbCyan
CellsToBeChecked.Cells(i, 2).Font.ColorIndex = 2
Case 50 To 65
CellsToBeChecked.Cells(i, 2).Interior.Color = vbYellow
CellsToBeChecked.Cells(i, 2).Font.ColorIndex = 2
Case Else
CellsToBeChecked.Cells(i, 2).Interior.Color = vbBlack
CellsToBeChecked.Cells(i, 2).Font.ColorIndex = 2
End Select

Next i
End Sub


Any help is greatly appreciated

Many thanks
David

Do you run this code from a commandbutton from the control toolbox toolbar? Or
any control from that toolbar?

If yes, try changing the .takefocusonclick property to false for that
commandbutton. (under rightclick properties).

If it's a control that doesn't have that .takefocusonclick property, you can
add:

activecell.activate

at the top of your code.

(In fact, this'll work for the commandbutton, too.)

It's a bug in xl97 that was fixed in xl2k.



David Goodall wrote:

Hi,
I've written some code that alters the interior of a cell colour based on
certain criteria. The code works fine in Excel 2003 but in Excel 97 I
sometimes get runtime error 1004 relating to the Interior class. I do need
it to work in excel 97.

I run the Excel 97 on a laptop running Windows 98, and Excel 2003 on my
desktop running win xp pro service pack 2.
The problem code is as follows:

Sub AgeCriteriaChecker()
Dim CellsToBeChecked As Range 'this is the range that contains the ages
Set CellsToBeChecked = Range(Range("C2"), Range("C65536").End(xlUp))
Dim i , Age As Integer

For i = 1 To CellsToBeChecked.Cells.Count
Age = CellsToBeChecked.Cells(i).Value

Select Case Age
Case 20 To 34
CellsToBeChecked.Cells(i, 2).Interior.Color = vbRed
CellsToBeChecked.Cells(i, 2).Font.ColorIndex = 2

Case 35 To 49
CellsToBeChecked.Cells(i, 2).Interior.Color = vbCyan
CellsToBeChecked.Cells(i, 2).Font.ColorIndex = 2
Case 50 To 65
CellsToBeChecked.Cells(i, 2).Interior.Color = vbYellow
CellsToBeChecked.Cells(i, 2).Font.ColorIndex = 2
Case Else
CellsToBeChecked.Cells(i, 2).Interior.Color = vbBlack
CellsToBeChecked.Cells(i, 2).Font.ColorIndex = 2
End Select

Next i
End Sub

Any help is greatly appreciated

Many thanks
David

--

Dave Peterson