Shade Columns based on criteria in cell.
 

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

Shade Columns based on criteria in cell.
 

First part, conditional formatting.

Select all the cells, B5:DD56, and in CF add a formula of

=B$4="S"

and format as required.

Second part needs formulas

G10: = IF(G$4="S","P","")

etc.

--
HTH

Bob Phillips

"Bill" wrote in message
...
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

Shade Columns based on criteria in cell.
 

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

Shade Columns based on criteria in cell.
 

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

Shade Columns based on criteria in cell.
 

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

Shade Columns based on criteria in cell.
 

Norman

It didn't work. I use a funtion to input the S in the cell whenever cell A4
changes. The function inserts a S when a 1 or 7 is the result of the
function. I do not use the CF because other users tend to play with it
there. I was using the Cf as Bob suggested but I have to expand at times and
the VBA allows be to add to the colum heading to keep it going.

Thanks for your help.

"Norman Jones" wrote:

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

Shade Columns based on criteria in cell.
 

Hi Bob,

Absolutely!

However, I interpreted:

Can this be done using VBA code instead CF?

literally.

As for the advantage of employing CF from Excel or drom VBA, I have already
nailed my flag to the mast.


---
Regards,
Norman



"Bob Phillips" wrote in message
...
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

Shade Columns based on criteria in cell.
 

Hi Bill,

It didn't work. I use a funtion to input the S in the cell whenever cell
A4
changes.

In my initial reply, I said:

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

If this is not the case, post back.

Replace the previous code with:

'==========================
Private Sub Worksheet_Calculate()

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")

Application.ScreenUpdating = False

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

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

Application.ScreenUpdating = True

End Sub
'<<==========================


---
Regards,
Norman



"Bill" wrote in message
...
Norman

It didn't work. I use a funtion to input the S in the cell whenever cell
A4
changes. The function inserts a S when a 1 or 7 is the result of the
function. I do not use the CF because other users tend to play with it
there. I was using the Cf as Bob suggested but I have to expand at times
and
the VBA allows be to add to the colum heading to keep it going.

Thanks for your help.

"Norman Jones" wrote:

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