Shade Columns based on criteria in cell.

[Guest]

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

 

[Bob Phillips]

First part, conditional formatting.

Select all the cells, B5D56, 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.

 

[Guest]

Can this be done using VBA code instead CF?

 

[Norman Jones]

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

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.

 

[Bob Phillips]

Hi Norman,

You could always have used VBA to set CF

Bob

 

[Guest]

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]

Hi Bob,

Absolutely!

However, I interpreted:

literally.

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

 

[Norman Jones]

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:

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

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
'<<==========================