Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting worksheet macro to regular macro
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting worksheet macro to regular macro
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting worksheet macro to regular macro
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 _ _ ^ąŻ^ -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting worksheet macro to regular macro
You have more replies at your original thread.
"wammer <" wrote: 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 --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert a Pivot Table to a regular Excel worksheet? | Excel Discussion (Misc queries) | |||
Excel Macro Issue Trying to autorun Macro Upon Opening Worksheet | Excel Programming | |||
How to end macro on inital active worksheet containing macro button that was clicked | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming | |||
How? Macro to copy range to new worksheet, name new worksheet, loop | Excel Programming |