Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
shade one cell that will shade multiple cells | Excel Discussion (Misc queries) | |||
Add a column based on criteria from 2 other columns | Excel Worksheet Functions | |||
Count based on criteria from two different columns | Excel Discussion (Misc queries) | |||
Need to count based on different criteria in two columns | Excel Worksheet Functions | |||
is there a way I can have excel shade a cell a color based on tha. | Excel Programming |