Excel Worksheet SelectionChange

[fredka]

I wanted to bold a few cells based on the result of another cell. Specifically:

Column K holds a percentage that the user updates. If that cell is greater than the corresponding cell in column P, I want a few cells to be bold. (same thing for columns ac and column w)

I created a procedure in the "worksheet_selectionchange" sub. Basically, I have a Loop until - It works fine - however, when I go to print preview and back to the worksheet, it seems like it just keeps looping and does nto stop? Any help would be greatly appreciated!! Is there a way to use an after-update type of event of the specific cells?

Here is the code:

Dim P As Integer
P = 7

'changes color of font for overall tier and bonus dollars
Do

If (Cells(P, "k") <> "") Then
If (Cells(P, "k") >= Cells(P, "ac").Value) Then

Cells(P, "Af").Font.Bold = True
Cells(P, "Ae").Font.Bold = True
Cells(P, "Ad").Font.Bold = True
Cells(P, "Ac").Font.Bold = True
Cells(P, "z").Font.Bold = False
Cells(P, "y").Font.Bold = False
Cells(P, "x").Font.Bold = False
Cells(P, "w").Font.Bold = False
Cells(P, "s").Font.Bold = False
Cells(P, "r").Font.Bold = False
Cells(P, "q").Font.Bold = False
Cells(P, "p").Font.Bold = False

ElseIf (Cells(P, "k") >= Cells(P, "w").Value) Then
Cells(P, "Af").Font.Bold = False
Cells(P, "Ae").Font.Bold = False
Cells(P, "Ad").Font.Bold = False
Cells(P, "Ac").Font.Bold = False
Cells(P, "z").Font.Bold = True
Cells(P, "y").Font.Bold = True
Cells(P, "x").Font.Bold = True
Cells(P, "w").Font.Bold = True
Cells(P, "s").Font.Bold = False
Cells(P, "r").Font.Bold = False
Cells(P, "q").Font.Bold = False
Cells(P, "p").Font.Bold = False

ElseIf (Cells(P, "k") >= Cells(P, "p").Value) Then

Cells(P, "Af").Font.Bold = False
Cells(P, "Ae").Font.Bold = False
Cells(P, "Ad").Font.Bold = False
Cells(P, "Ac").Font.Bold = False
Cells(P, "z").Font.Bold = False
Cells(P, "y").Font.Bold = False
Cells(P, "x").Font.Bold = False
Cells(P, "w").Font.Bold = False
Cells(P, "s").Font.Bold = True
Cells(P, "r").Font.Bold = True
Cells(P, "q").Font.Bold = True
Cells(P, "p").Font.Bold = True
Else
Cells(P, "Af").Font.Bold = False
Cells(P, "Ae").Font.Bold = False
Cells(P, "Ad").Font.Bold = False
Cells(P, "Ac").Font.Bold = False
Cells(P, "z").Font.Bold = False
Cells(P, "y").Font.Bold = False
Cells(P, "x").Font.Bold = False
Cells(P, "w").Font.Bold = False
Cells(P, "s").Font.Bold = False
Cells(P, "r").Font.Bold = False
Cells(P, "q").Font.Bold = False
Cells(P, "p").Font.Bold = False
End If
End If

P = P + 1

Loop Until P = 38

 

[PHV]

Why not simply use conditional formatting ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredka]

Hi - I actually did realize that I could accomplish this task easier with conditional formatting - However, I was hoping to find why my code is locking up for future ref.

thanks!!!!

Fred

 

[PHV]

Is there a way to use an after-update type of event of the specific cells?
Have a look at the Worksheet_Change event procedure and the Intersect method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Daamann]

i would try stepping through code, while watching the value of P. does it ever get to value of 38 ?
what happens after value hits 38 ?

watch to see where it strays to.