Coverting worksheet macro to regular macro

[wammer]

I'm having toruble converting this to a regular macro - how would I d
this?

Private Sub Worksheet_Calculate()

Dim myColorIndex As Long
Dim myCell As Range
Dim myRng As Range

Set myRng = Me.Range("o5:Iv2232")

For Each myCell In myRng.Cells
Select Case LCase(myCell.Value)
Case Is = "b": myColorIndex = 3
Case Is = "v": myColorIndex = 5
Case Else: myColorIndex = xlNone
End Select
myCell.Interior.ColorIndex = myColorIndex
Next myCell

End Su

 

[Markus Stolle]

Simply Remove the "PRIVATE"

Then the code shows up in your list of macros

Is that what you needed?



Further, is the Range you are referring to allways exactly that size
Or can it grow or shrink?

If you create a Macro, you normally want to ba able to cope with range
that change in size.

If the Range that you are working with is a continious block of cells
you might want to try the .currentregion property

Which references all cells that make out the block in which you fin
the specified range.


set MyRng = me.range("o5").currentregio

 

[Guest]

Hi,

Try in your Standard Code Module

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Sub TEST()

Dim myColorIndex As Long
Dim myCell As Range
Dim myRng As Range
Dim i As Long
Dim REF As String
Dim X As Variant
Dim N As Variant

X = Array("b", "v")
N = Array(3, 5)

Application.ScreenUpdating = False
Set myRng = ActiveSheet.Range("o5:Iv2232")
myRng.Interior.ColorIndex = myColorIndex

For i = LBound(X) To UBound(X)

Set myCell = myRng.Find(X(i), LookIn:=xlValues)
If myCell Is Nothing Then GoTo e:

REF = myCell.Address
Do
myCell.Interior.ColorIndex = N(i)
Set myCell = myRng.FindNext(myCell)
Loop While Not myCell Is Nothing And myCell.Address <> REF
Next

e:
Application.ScreenUpdating = True

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[Dave Peterson]

You have more replies at your original thread.

I'm having toruble converting this to a regular macro - how would I do
this?

Private Sub Worksheet_Calculate()

Dim myColorIndex As Long
Dim myCell As Range
Dim myRng As Range

Set myRng = Me.Range("o5:Iv2232")

For Each myCell In myRng.Cells
Select Case LCase(myCell.Value)
Case Is = "b": myColorIndex = 3
Case Is = "v": myColorIndex = 5
Case Else: myColorIndex = xlNone
End Select
myCell.Interior.ColorIndex = myColorIndex
Next myCell

End Sub