• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

VBA looping problem

Joined
12/1/07
Messages
22
Points
11
Hi, :sos:
Any idea what can be wrong with this piece of code? I want to make the cell I am changing bold but it looks like it is looping and before I get my desired result it changes bold-not bold-bold-not bold for about like 20 times. I would appreciate some advice.


Private Sub Worksheet_Change(ByVal Target As Range)
S_Row = Target.Row

If IsEmpty(Cells(S_Row, 5)) Or IsEmpty(Cells(S_Row, 6)) Then Exit Sub

If Intersect(Target, Cells(S_Row, 5)) Is Nothing And Intersect(Target, Cells(S_Row, 6)) Is Nothing Then
Exit Sub
Else
If Intersect(Target, Cells(S_Row, 5)) Is Nothing Then
Cells(S_Row, 5) = Cells(S_Row, 6) * Cells(S_Row, 4)
Cells(S_Row, 6).Font.Bold = True
Cells(S_Row, 5).Font.Bold = False


Else
Cells(S_Row, 6) = Cells(S_Row, 5) / Cells(S_Row, 4)
Cells(S_Row, 5).Font.Bold = True
Cells(S_Row, 6).Font.Bold = False

End If
End If

End Sub
 
Did you try using a conditional formatting formula before this?

Tools -> Conditional Format

formula: "=mod(row(),6)=1"

and choose the format as bold.

Does this solve your problem? Please correct me if I misunderstand your problem.
 
Hm...Hi Chris...I am not sure if I am understanding your approach either. :) Let me clear it up.
I have two fields one $ strike and one % strike. I can enter either one and the other will be calculated based on my new entry. To mark that I entered $ amount I want to make it bold. If afterwards I enter % I want % to be bold and not $ amount. So they are mutually exclusive; if one is bold the other one can't be. I used the sub Worksheet_Change to have the corresponding field recalculated when I change one and to make it bold on change.

The formula you gave me: should I enter it exactly as you wrote it down? Nothing is happening when I use it.
 
The formula I wrote won't work for that. Now that I understand your problem, let me give it a go and I'll post my results later.
 
You don't need VBA to do that. Write add a third cell whose value is 0 if you have nothing entered, 1 if dollar amount only, 2 if % amount, 3 if both.

C++:
=if( AND( A5="", B5="" ), 0, IF( AND( A5<>"", B5="" ), 1, IF( AND( A5="", B5<>"", 2, 3 )))
Where A5 has the $ cell and B5 has the % cell.

Then do conditional formatting to bold or unbold depending on the state. (i.e. if A7 has the formula above, use conditional formatting to bold if A7=1 for A5, etc. etc.) If both are entered, you can have additional logic.

Worksheet change is probably a bad way to do anything, especially if you have live data, as it will be hitting constantly.
 
Ok. I figured that out.

I added at the beginning "Application.EnableEvents = False" and at the end
"Application.EnableEvents = True" to stop the sub from firing off every time there was a change within the code.

Thank you for helping out Chris.
 
Good point. I don't have live data though. At least not at this time.

The problem is that at all times there is something entered in both cells; they are never empty. I need one to respond to the change in the other.
 
Are you actually entering the "$" and "%" characters?

Maybe you should post a small example sheet, if you haven't already solved your problem.
 
Back
Top