A real easy one VBA

[The Idea Dude]

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?

 

[gnaga]

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]

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]

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]

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]

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]

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

 

[The Idea Dude]

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]

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