Counting text and launching macro problems

[kotting]

I hope this is thorough enough (as will be evident in a moment), I am a complete novice working from books.

I am trying to devise a macro to lauch when the cell status changes on an excel worksheet(the input is text). Also I have a working model of the procedure I am going to use but it does not fit with all the requirements I would LIKE to have (but it will do). An enhancement that would be handy is for the macro to look at the whole range (not just a single cell) and count the number of times text appears in the given range. Based on the result shade or not shade (see below).

This macro I will copy to fill in a whole month of 9 shift 24 hour periods.

/////////////////////////////////////////////

Sub StoreObject()
Sheets("Sheet2").Select
myObject = Range("E7")
Set myObject = Range("E7")
If myObject <> Empty Then
Range("E7:F7,E7:H7,I11:L11,M15:P15").Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If
End
End Sub

/////////////////////////////////////////////

Thanks for any insights - Just to be clear, My primary question is how to launch the macro when the cell changes.

Kotting

 

[Dave Hawley]

Hi Kotting

For your code try:

Sub StoreObject()

If Not IsEmpty(Sheets("Sheet2").Range("E7")) Then
With Range("E7:F7,E7:H7,I11:L11,M15:P15").Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If

End Sub


For your primary question, right click on the sheet name tab, select "View Code" and try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
'Your Code Here

End If
End Sub


_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-03-25 07:15

 

[kotting]

Thank you!

I will try it out.