Need help getting macro to run when a cell contains certain text

[GoodWithExcelNotVBA]

Hi,

I'm new to this forum, and to VBA really, and I'm trying to format a range of cells a certain way once a separate cell has a certain text entered into it (chosen from a list), such as "Welcome Home". I've pieced together a few bits of code that I found hunting around for my answer, but I can't seem to get it to work the way I want (the specific code shown below doesn't even work, but I feel it's closest to where I'm trying to get). Again, I have no programming knowledge, so there are probably some extraneous pieces of code in here, but here is my starting point:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("B53")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing And Target.Range = "Welcome Home" Then

Range("A54:F62").Select
With Selection.Interior
.ColorIndex = 48
.Pattern = xlSolid
End With
Rows("54:62").Select
Selection.RowHeight = 10
Range("B64").Select

End If
End Sub


I used the record macro feature to get the formatting part the way I want it, so that part is not an issue. I just need to try to figure out the trigger issue -- if it's even possible. Thanks so much for any help!

Jordan

 

[lenze]

Hi and welcome to the board!!
Try something like this in the WorkSheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$53" Then Exit Sub
Dim iCol As Variant
Dim myHgt As Long
Select Case Target
Case "Welcome Home": iCol = 48: myHgt = 10
Case "Goodbye": iCol = 6: myHgt = 12 : myHgt = 11
'etc
Case Else: iCol = xlNone
End Select
Range("$A454:$F$62").Interior.ColorIndex = iCol
Rows("54:62").RowHeight = myHgt
End Sub

Notice that not once does the code Select anything. It is very seldom you need to Select when using VBA!!

lenze

 

[lenze]

Just noticed a typo in my code! This

Case "Goodbye": iCol = 6: myHgt = 12 : myHgt = 11

should just be

Case "Goodbye": iCol = 6: myHgt = 12

and This

Case Else: iCol = xlNone

should be

Case Else: iCol = xlNone: myHgt=11

lenze

 

[GoodWithExcelNotVBA]

Lenze,

Thanks so much for the response! I apologize for the "selects", haha, but that was just how the Record Macro feature coded it.

Your code seems to be putting me on the right course, but I have two questions:

1) Can I put in something like:

Case "Goodbye": iCol = 6: myHgt = AutoFit: myHgt = AutoFit


and,

2) Why can't I make something like Case "Some sort of sentence here with & and it's" work? Is there a word or character limit?

Thanks again!

 

[GoodWithExcelNotVBA]

Lenze,

I hate to keep bugging you and asking for help (but I do tremendously appreciate it!), but I now need the Case Else option to go straight to something like:

Rows("54:62").Select
Rows("54:62").EntireRow.AutoFit
Range("B54:B62,F54:F62").Select
Selection.Interior.ColorIndex = 34
Range("A54:A62,C54:E62").Select
Selection.Interior.ColorIndex = xlNone

Without all of the selects, of course, if you would rather it that way!

Thanks again; I truly appreciate you taking time to help me out.

Jordan

 

[GoodWithExcelNotVBA]

Can anyone else help me figure this out? I've incorporated Lenze's code into my sheet, but I'm still encountering the problems listed below:

1) Can I put in something like:

Case "Goodbye": iCol = 6: myHgt = AutoFit

and,

2) Why can't I make something like Case "Some sort of sentence here with & and it's" work? Is there a word or character limit?




And I also need to change the "case else" section to make it achieve the results of the following code:

Rows("54:62").Select
Rows("54:62").EntireRow.AutoFit
Range("B54:B62,F54:F62").Select
Selection.Interior.ColorIndex = 34
Range("A54:A62,C54:E62").Select
Selection.Interior.ColorIndex = xlNone


Any help would be much appreciated!

 

[lenze]

Q1: Do you want AutoFit for all Cases??
Q2: I know of no reason you can't use a sentence(Length limit??), but I think it would be bad practice!! Select Case requires an exact match and any typo, including capitalization, will case it to fail!! What are you realy trying to do?

For your Case Else, try

Rows("54:62").EntireRow.AutoFit
Range("A54:F62").Interior.ColorIndex = xlNone
Range("B54:B62,F54:F62").Interior.ColorIndex = 34

lenze

 

[GoodWithExcelNotVBA]

Thanks again Lenze for the response. I can't seem to make the Case Else work correctly. Here is the code I have currently:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$53" Then Exit Sub
Dim iCol As Variant
Dim myHgt As Long
Select Case Target
Case "Existing MTA": iCol = 48: myHgt = 10
'etc
Case Else: Rows("54:62").EntireRow.AutoFit
Range("A54:F62").Interior.ColorIndex = xlNone
Range("B54:B62,F54:F62").Interior.ColorIndex = 34
End Select
Range("$A54:$F$62").Interior.ColorIndex = iCol
Rows("54:62").RowHeight = myHgt
End Sub

Currently, the Case "Existing MTA" works correctly, but if anything "else" is chosen from the drop down menu for the cell, then it just hides rows 54-62.

That's one problem. The other, is when I type into the code my desired case of "OUR P&C's - PROCEED TO NEXT SECTION" (which is also chosen from a drop down menu, so it will always be the exact text), the case stops working and just does nothing. It's got me quite befuddled!

I do appreciate your help, and I feel like you helped me learn a good deal so far!

 

[lenze]

For the Case Else, you need to exit the Sub

Case Else: 
    Rows("54:62").EntireRow.AutoFit
    Range("A54:F62").Interior.ColorIndex = xlNone
    Range("B54:B62,F54:F62").Interior.ColorIndex = 34
Exit Sub
End Select

So your code with the new case would be something like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$53" Then Exit Sub
Dim iCol As Variant
Dim myHgt As Long
Select Case Target
Case "Existing MTA": iCol = 48: myHgt = 10
Case "OUR P&C's - PROCEED TO NEXT SECTION": iCol = 4: myHgt = 13
'etc
Case Else
    Rows("54:62").EntireRow.AutoFit
    Range("A54:F62").Interior.ColorIndex = xlNone
    Range("B54:B62,F54:F62").Interior.ColorIndex = 34
    Exit Sub
End Select
Range("$A54:$F$62").Interior.ColorIndex = iCol
Rows("54:62").RowHeight = myHgt
End Sub

Remember, as the code is written, it only works when a change is made in B53. and it must match EXACTLY!!!!

lenze

 

[GoodWithExcelNotVBA]

That's perfect! Exactly what I needed. Thanks so much Lenze.

It's still bugging me that I couldn't make Case "long sentence" thing work before, but I guess it's best just not to worry sometimes.

Thanks again for your help!


Jordan