Private Sub Worksheet Change by Val looping

cvincent

Board Regular
Joined
Aug 28, 2011
Messages
66
I am not an expert, but managed to create these two vbas that work fine, except that it loops after every single cell change, and I probably need to combine into one vba. I only need to look at C54:D54. The tab color change feature needs to look at cells C54:D54 (not A56), and if the value is "HOLD", color the tab blue, and otherwise do not color the tab. Cell A56 simply says (if C54:D54 = "HOLD", "HOLD","") - I think I can bypass that and just look at C54:D54 (a dropdown selection).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C54:D54")) Is Nothing Then
Application.Run "RemoveCover"
End If
End Sub
Private Sub Worksheet_Calculate()

If Range("A56").Value = "" Then
Me.Tab.ColorIndex = -4142 ' No Color
Else
Me.Tab.ColorIndex = 23 ' Blue
End If

End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,212,933
Messages
6,110,759
Members
448,295
Latest member
Uzair Tahir Khan

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top