Hi Norman,

You could always have used VBA to set CF :-)

Bob

"Norman Jones" wrote in message
...
Hi Bill,

Personally, I think that Bob's suggestion is the optimal solution. What
have
you got against CF?

For a VBA solution try:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, rng2 As Range
Dim rCell As Range
Dim i As Long
Dim arr As Variant

Const TriggerLetter As String = "S"

arr = Array("P", "A", "Y", "S", "T", "O", "P")

Set rng = Me.Range("B4:DD4")

If Not Intersect(Target, rng) Is Nothing Then

For Each rCell In rng.Cells
If UCase(rCell.Value) = TriggerLetter Then
rCell(2).Resize(51).Interior.ColorIndex = 15
For i = 0 To UBound(arr)
Cells(i + 10, rCell.Column).Value = arr(i)
Next
rCell(7).Resize(7).Font.Bold = True
Else
rCell(2).Resize(51).Interior.ColorIndex = xlNone
rCell(7).Resize(7).ClearContents
End If
Next
End If

End Sub

This is worksheet event code and needs to be placed in a worksheet
module,
not in a standard module.

right-click the worksheet tab ! 'View Code' | paste the above code |
Alt-F11 to return to the worksheet.

This procedure assumes that values are manually entered in row 4.

If this is not the case, post back.


---
Regards,
Norman



"Bill" wrote in message
...
Can this be done using VBA code instead CF?

"Bill" wrote:

Help Please.

I have a spreadsheet that has numbers in row 4 starting with column B
and
extending to column DD. When the contents of row 4 for that column =
S,
I
then need to shade that column starting with row 5 through row 56 to
the
color gray.

And if possible, when the above occurs insert the following letters in
the
cells for that column in the rows as shown below.

An example is column G row 4 = S then

G10 value = P
G11 value = A
G12 value = Y
G13 value = S
G14 value = T
G15 value = O
G16 value = P

Thanks

Bill