Loop question

Daisy_Bumbleroot

New Member
Joined
Nov 24, 2004
Messages
35
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?
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
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

New Member
Joined
Nov 24, 2004
Messages
35
psitaram said:
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

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
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

New Member
Joined
Nov 24, 2004
Messages
35
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....
 

Watch MrExcel Video

Forum statistics

Threads
1,122,841
Messages
5,598,388
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top