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

GoodWithExcelNotVBA

New Member
Joined
Jul 26, 2010
Messages
7
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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Hi and welcome to the board!!
Try something like this in the WorkSheet module
Code:
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

Legend
Joined
Feb 18, 2002
Messages
13,690
Just noticed a typo in my code! This
Code:
Case "Goodbye": iCol = 6: myHgt = 12 : myHgt = 11
should just be
Code:
Case "Goodbye": iCol = 6: myHgt = 12
and This
Code:
Case Else: iCol = xlNone
should be
Code:
Case Else: iCol = xlNone: myHgt=11

lenze
 

GoodWithExcelNotVBA

New Member
Joined
Jul 26, 2010
Messages
7
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

New Member
Joined
Jul 26, 2010
Messages
7

ADVERTISEMENT

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

New Member
Joined
Jul 26, 2010
Messages
7
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

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

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
Code:
Rows("54:62").EntireRow.AutoFit
Range("A54:F62").Interior.ColorIndex = xlNone
Range("B54:B62,F54:F62").Interior.ColorIndex = 34

lenze
 

GoodWithExcelNotVBA

New Member
Joined
Jul 26, 2010
Messages
7
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

Legend
Joined
Feb 18, 2002
Messages
13,690
For the Case Else, you need to exit the Sub
Rich (BB code):
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
Rich (BB code):
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

New Member
Joined
Jul 26, 2010
Messages
7
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,606
Messages
5,597,134
Members
414,128
Latest member
Jorglo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top