# Instead of UDF I need to develop Sub in Personal.XLSB

#### Bamerand

##### Board Regular
A question to clarify...

I need to perform calculation in a particular cell that is located in a row (for example, in the table I provided here, it is "W1"), where variables are changed by value, fill and font color. I have a set of table (B2:V20) with values populated such as "11" and I need to calculate when I change values in either one or more rows. I cannot use "=SUM()" function for the values are to be calculated based on the font color, cells interior color, fill etc. Before I used UDF functions that made computations based on font color and the cells interior color. But because of huge amount of data, the computation goes a bit difficult, i.e. by changing 1 or 2 or more value, the UDF calculate the entire sheet / workbook.

In order to save time and avoid calculating the whole sheet / workbook, I need to come with a procedure, not function, but will calculate / sum the row, where change took place only.Here is a sample of the code I used to calculations. I figured how to use PERSONAL.XLSB and store the code in the personal book and call it via buttons assigned via MS Excel menu.

I need to redo the following "Function ORT" into "Procedure ORT"

Function ORT(Rng As Range) As Long
Dim Cell As Range Application.Volatile
For Each Cell In Rng If Cell.Font.Color = 16711680 And Cell.Value = ("11") And Cell.Font.Bold = True _
And (Cell.Interior.Color = (13434828)) Then ORT = ORT + 1 Next Cell
End Function

I assume that changing Function ORT to Procedure ORT, plus code Application.Run "Personal.xlsb!Function ORT", which will run this piece of code

Private Sub MyMacro1()
Application.ScreenUpdating = False
Selection.Cells.Font.Color = 16711680
Selection.Cells.Value = ("11")
Selection.Cells.Font.Bold = True
Selection.Cells.Interior.Color = 13434828
Application.CalculateFull
Application.Run "Personal.xlsb!Function ORT"
Application.ScreenUpdating = True
End Sub

Now here come the hardest part - the code needs to calculate only the selected / changed rows, i.e. if I make changes in 1st row, then it should calculate the first row changes, if I do 1st and 2nd rows, then the code must calculate 1st and second rows. I believe there is a way to use ByVal Target As Range, but I am not sure how to use it (gave a try, no luck).Can anybody give me a clue on how to do it? Any links, articles or posts are welcome, sharing your thoughts in particular...

Regards,

Bamerand

(formating of the table is slightly different from the code specified, but that should go without saying)....

 B C D E F G H I J K L M N O P Q R S T U V W 1 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 55 2 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 3 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 4 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 5 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 6 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 7 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 8 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 9 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 10 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 12 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 13 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 14 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 15 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 16 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 17 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 18 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 19 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11

<colgroup><col><col span="21"><col></colgroup><tbody>
</tbody>

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Try this

Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("b24:B100,e24:e100")) Is Nothing Then 'direct the range you want to monitor
Sheets("reference lookup").Range("o4") = Target.Cells 'I use this to pick up the value under the cursor and place on a sheet
Selection.NumberFormat = "General"
End If
Exit Sub

Replies
5
Views
382
Replies
1
Views
306
Replies
6
Views
556
Replies
1
Views
245
Replies
5
Views
210

1,203,051
Messages
6,053,219
Members
444,648
Latest member
sinkuan85

### 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.

### Which adblocker are you using?

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

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