Macro Multiplying Cells Depending on Data Validation
Posted by Wilma on March 23, 2001 9:35 AM
I have a worksheet in Excel 97. I have seven cells set up with data validation in column F from F25 to F31. Each of these seven cells only accept the following words: Shoe, Shirt, Tie and Total. I have another column to enter amounts (numbers). This column is made up of cells J25 to J31. I want Excel to do the following: That any number I enter in J25 be multiplied in this same cell by 0.12 only when it sees the word Shirt in F25 (its adyacent cell); but if the same thing happens entering any number in cells J26 thru J31 and the same word (Shirt) is seen in in their adyacent F26 thru F31 cells, the number entered should be multiplied by 0.12. I also want Excel to do the same if the word selected is Shoe instead of Shirt. In this case, the multiplication should be by 0.25 instead of 0.12. Could anybody help? Your answer will be appreciated. Thanks.
Posted by Dave Hawley on March 23, 2001 7:59 PM
Hi Wilma
You would use the Sheet Chnage event for this.
Right click on your sheet name tab and select "View Code", then paste in this code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range, Rw As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Not IsNumeric(Target) Then Exit Sub
Set WatchRange = Range("J25:J31")
If Not Intersect(Target, WatchRange) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
Rw = Target.Row
Select Case Range("K" & Rw)
Case "Shirt"
Target = Target * 0.12
Case "Shoe"
Target = Target * 0.25
End Select
Application.EnableEvents = True
On Error GoTo 0
Set WatchRange = Nothing
End If
Just add more "Case" statements if needed.
Dave
OzGrid Business Applications
