VBA looping problem

[Urszula]

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

 

[Christian]

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.

 

[Urszula]

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.

 

[Christian]

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.

 

[doug reich]

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.

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

 

[Urszula]

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.

 

[Urszula]

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.

 

[charlesdwright]

Are you actually entering the "$" and "%" characters?

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