Loop question

[Daisy_Bumbleroot]

Why do i get Runtime error =13, type mismatch after running this macro?

Dim colournumber As Integer
Range("C3").Select
Do While ActiveCell.Offset(1, 0) <> ""
If ActiveCell <> "" Then
colournumber = 2
If ActiveCell = "reliability" Then
colournumber = 4
End If
If ActiveCell = "General" Then
colournumber = 6
End If
If ActiveCell = "Safety" Then
colournumber = 3
End If
If ActiveCell = "No Priority Identification" Then
colournumber = 8
End If
ActiveCell.Offset(0, -2).Activate
Selection.Interior.ColorIndex = colournumber
ActiveCell.Offset(0, 1).Activate
Selection.Interior.ColorIndex = colournumber
ActiveCell.Offset(0, 1).Activate
Selection.Interior.ColorIndex = colournumber
ActiveCell.Offset(1, 0).Activate
End If
Loop
End Sub



also, can i activate a RANGE to colour instead of a colouring cell by cell?

 

[P Sitaram]

Instead of Range("C3"). Select, set the range to a range variable and use the variable instead of `Selection' object. You can also use Range("C3") directly.

 

[Daisy_Bumbleroot]

Instead of Range("C3"). Select, set the range to a range variable and use the variable instead of `Selection' object. You can also use Range("C3") directly.

Sorry, i only have dead basic understanding of VB.

i understand what you mean, but i dont know how to code it?

 

[P Sitaram]

1. At the beginning of the procedure, add the two lines:

Dim MyRange as Range
Set MyRange = [C3]
...

Remove the line Range("C3").Select

In the rest of the code, change `Selection' to `MyRange'

2. For the first If, the End If is right at the end of the code, before the loop. This means that the entire code is run if ActiveCell <> "". Is this what you want?

3. See the VBE help for the Select Case statement, which may be useful in your context.

 

[Daisy_Bumbleroot]

select case forgot about that - TA!

does anyone know why i get the error message?

it works all the way down to the second last cell and i get a type mismatch....