compare values and msg box for permutation

**SarahPintal** · 05-27-2010, 12:56 AM

Hi guys,

I hope someone can help..

I need a macro that will check if the word in column J5, J6, J7. (what ever cell has been changed in column J) is the same or differnt that the one in column A on the same row.

If differnt then for a specific msg to pop up to each permuation.

column A has a drop down list with 3 names, and column J has that same drop down list with the same 3 names.

I want to be able to have a msg box pop up is in column A5 (for example) the work sarah is entered and in J5 the worked Mary is entered, for the msg box to say "mary is Sarahs mum" ... if Mary is in A6 and Sarah is in J6 upon selecting Sarah a msg box would this say "Mary is Sarahs mum"..

The words are Sarah, Mary, John

Is that possible... ? thanks guys

**SarahPintal** · 05-27-2010, 02:53 AM

I tried this for an initial tester - see the KeyCellsChanged sub..... nothing happens though.

 Sub auto_open()

   ' Run the macro DidCellsChange any time a entry is made in a
   ' cell in Sheet1.
   ThisWorkbook.Worksheets("Dashboard").OnEntry = "DidCellsChange"

End Sub


Sub DidCellsChange()
  Dim KeyCells As String
   ' Define which cells should trigger the KeyCellsChanged macro.
   KeyCells = "K:K"

   ' If the Activecell is one of the key cells, call the
   ' KeyCellsChanged macro.
   If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
   Is Nothing Then KeyCellsChanged

End Sub

Sub KeyCellsChanged()
   Dim Cell As Object
  
   For Each Cell In Range("K:K")
   
   If ActiveCell.Value = "Full" & ActiveCell.Offset(0, -7).Value = "Reduced" Then
    
MsgBox "you are reducing your service set from Full to Reduced", vbwarning, "title"

   End If
   Next Cell

End Sub

**SarahPintal** · 05-27-2010, 07:57 PM

Okay so this works, though it would be great if someone couple please assist with helping me make it so that if any cell K:K is changed then for the below sub to be called.

please help, thank you , S

 Sub KeyCellsChanged()
   Dim Cell As Object

   If ActiveCell.Value = "Reduced" And ActiveCell.Offset(0, -7).Value = "Full" Then
        MsgBox "you are reducing your service set from Full to Reduced", vbwarning, "title"
                    
                    ElseIf ActiveCell.Value = "Reduced" And ActiveCell.Offset(0, -7).Value = "Bug Fix" Then
                            MsgBox "you are Increasing your service set from Bug Fix to Reduced", vbwarning, "title"
  
End If

End Sub

**SarahPintal** · 05-27-2010, 08:07 PM

well it seems that silence has solved this.

I worked it out, this is what I did for anyone that wants to know;
This is 1 the worksheets code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Column = 11 Then KeyCellsChanged3
End Sub

and this is in module 1

Sub KeyCellsChanged3()
   Dim Cell As Object
 
   If ActiveCell.Value = "Reduced" And ActiveCell.Offset(0, -7).Value = "Full" Then
        MsgBox "you are reducing your service set from Full to Reduced", vbwarning, "title"
                    
                    ElseIf ActiveCell.Value = "Reduced" And ActiveCell.Offset(0, -7).Value = "Bug Fix" Then
                            MsgBox "you are Increasing your service set from Bug Fix to Reduced", vbwarning, "title"
  
End If
 
End Sub