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

 

[GoodWithExcelNotVBA]

Ok, so I have a pretty good understanding of what all the code you wrote meant and how it worked, but I don't know how to incorporate a second round of that. I need the following code idea incorporated without overriding the original piece you wrote. Basically I have two separate target cells where if 1 changes then a certain set of rules applies, but if #2 changes a new set of rules apply that don't affect #1 at all. And I've been banging on the keys with no success for the past few hours.. any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$55" Then Exit Sub
Dim iCol As Variant
Dim myHgt As Long
Select Case Target
Case "PCR P&Q's - PROCEED TO NEXT SECTION": iCol = 48: myHgt = 10
'etc
Case Else
Rows("56:64").EntireRow.AutoFit
Range("A56:F64").Interior.ColorIndex = xlNone
Range("B56:B64,F56:F64").Interior.ColorIndex = 34
Exit Sub
End Select
Range("$A56:$F$64").Interior.ColorIndex = iCol
Rows("56:64").RowHeight = myHgt
End Sub
-----------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$78" Then Exit Sub
Dim iCol As Variant
Dim myHgt As Long
Select Case Target
Case "Proposal - PROCEED TO NEXT SECTION": iCol = 48: myHgt = 10
'etc
Case Else
Rows("79:85").EntireRow.AutoFit
Range("A79:F85").Interior.ColorIndex = xlNone
Range("B79:B85,F79:F85").Interior.ColorIndex = 34
Exit Sub
End Select
Range("$A79:$F$85").Interior.ColorIndex = iCol
Rows("79:85").RowHeight = myHgt
End Sub


This is how the code(s) appears in the VBA editor presently. I know this won't work, but I just wanted to get the idea across. Any help is much appreciated!

Jordan