A real easy one VBA

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
589
Lets suppose cell A5 has the number 7 in it.

What VBA code will run an existing macro should the number in cell A5 change?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
In your worksheet object copy this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
Call ' Your Existing Macro
End If
End Sub

GNaga
This message was edited by gnaga on 2002-09-11 05:31
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
589
A slight addition needed.

both Cell A5 needs to change, AND any value in the range B11:G15 cannot be text black colour (ie it needs to have coloured text)

Thanks!
 

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
Try this, you need to insert it as before in the worksheet change event.


BgColour = False
Set rng = Range("B12:G16")
For Each c In rng
If c.Interior.ColorIndex = 6 Then BgColour = True
Next
If (Target.Address = "$A$5" And BgColour = True) Then
'Call Your Existing Macro
End If
 

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675

ADVERTISEMENT

Just thought, the above works but this is more efficient!!

If Target.Address = "$A$5" Then
BgColour = False
Set rng = Range("B12:G16")
For Each c In rng
If c.Interior.ColorIndex = 6 Then BgColour = True
Next

if BgColour = true then
'Call Your Existing macro
End If

end if
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
I am making a small change in Chris Code with his approval since you dont to be text color in black not the background color.

If Target.Address = "$A$5" Then
BgColour = False
Set rng = Range("B11:G15")
For Each c In rng
If c.Font.ColorIndex <> 1 Then BgColour = True
Next

if BgColour = true then
'Call Your Existing macro
End If

end if

GNaga
 

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675

ADVERTISEMENT

Whoops, thanks. Always pays to read the post properly!
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
589
Thanks Chris and Gnaga.

This code doesn't quite do what I expected. When I play it from VBA a box pops up asking me which macro I want to choose, and then some options like run, step into, edit. If I select the macro I want and hit run, it does the job, however, I thought all this would happen automatically if cell A5 changed? When cell A5 changes, nothing happens.
Perhaps I am calling it incorrectly. This is my call code.

Call Colours

where colours() is the name of the sub that I want to run. Note: coulours is in module 1, and the other code (from this post) is in sheet 6.

Any suggestions
This message was edited by The Idea Dude on 2002-09-11 19:08
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
In VB environment click Sheet6 and then paste this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
BgColour = False
Set rng = Range("B11:G15")
For Each c In rng
If c.Font.ColorIndex <> 1 Then BgColour = True
Next

if BgColour = true then
Call Colours
end if
End Sub

This should work whenever cell A5 in sheet6 changes.

GNaga
 

Forum statistics

Threads
1,147,450
Messages
5,741,189
Members
423,647
Latest member
lyanndominique

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
Top