Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shading Rows With IF Statements?
I have a large spreadsheet and based on a status indicator in Column DW, I
need to shade the entire row one of eight colors, For example, if a cell value in row DW is L the whole row needs to be shaded gray. Another example. is if DW is F, the row needs to be Blue. If DW is blank, the row is not shaded. Is this doable with IF statements; I already use Conditional Formatting for another purpose. I am using Excel 2003 -- JPS |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shading Rows With IF Statements?
You need VBA.
'----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "DW:DW" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "L": .EntireRow.Interior.ColorIndex = 48 '40% grey Case "F": .EntireRow.Interior.ColorIndex = 5 'blue 'etc Case Else: .EntireRow.Interior.ColorIndex = xlColorIndexNone End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "JPS" wrote in message ... I have a large spreadsheet and based on a status indicator in Column DW, I need to shade the entire row one of eight colors, For example, if a cell value in row DW is L the whole row needs to be shaded gray. Another example. is if DW is F, the row needs to be Blue. If DW is blank, the row is not shaded. Is this doable with IF statements; I already use Conditional Formatting for another purpose. I am using Excel 2003 -- JPS |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shading Rows With IF Statements?
Bob,
Sorry for taking so long to respond I have been working on a rush project. Two considerations... Since I cannot spell VBA and the spreadsheet is maintained by my clerical staff, I am looking for a simpler solution. I have looked at the conditional formatting that I am currently using and I can handle another way. If I use conditional formatting can I get 6 colors, I would like 8 but I can manager with 6, and how do I shade the entire row. Thanks for your help. -- JPS "Bob Phillips" wrote: You need VBA. '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "DW:DW" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "L": .EntireRow.Interior.ColorIndex = 48 '40% grey Case "F": .EntireRow.Interior.ColorIndex = 5 'blue 'etc Case Else: .EntireRow.Interior.ColorIndex = xlColorIndexNone End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "JPS" wrote in message ... I have a large spreadsheet and based on a status indicator in Column DW, I need to shade the entire row one of eight colors, For example, if a cell value in row DW is L the whole row needs to be shaded gray. Another example. is if DW is F, the row needs to be Blue. If DW is blank, the row is not shaded. Is this doable with IF statements; I already use Conditional Formatting for another purpose. I am using Excel 2003 -- JPS |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shading Rows With IF Statements?
select the whole row and use a condition locked on one cell, such as
=$C210 -- __________________________________ HTH Bob "JPS" wrote in message ... Bob, Sorry for taking so long to respond I have been working on a rush project. Two considerations... Since I cannot spell VBA and the spreadsheet is maintained by my clerical staff, I am looking for a simpler solution. I have looked at the conditional formatting that I am currently using and I can handle another way. If I use conditional formatting can I get 6 colors, I would like 8 but I can manager with 6, and how do I shade the entire row. Thanks for your help. -- JPS "Bob Phillips" wrote: You need VBA. '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "DW:DW" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "L": .EntireRow.Interior.ColorIndex = 48 '40% grey Case "F": .EntireRow.Interior.ColorIndex = 5 'blue 'etc Case Else: .EntireRow.Interior.ColorIndex = xlColorIndexNone End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "JPS" wrote in message ... I have a large spreadsheet and based on a status indicator in Column DW, I need to shade the entire row one of eight colors, For example, if a cell value in row DW is L the whole row needs to be shaded gray. Another example. is if DW is F, the row needs to be Blue. If DW is blank, the row is not shaded. Is this doable with IF statements; I already use Conditional Formatting for another purpose. I am using Excel 2003 -- JPS |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shading Rows With IF Statements?
Bob,
Can I expand conditional formatting to get 6 colors? -- JPS "Bob Phillips" wrote: select the whole row and use a condition locked on one cell, such as =$C210 -- __________________________________ HTH Bob "JPS" wrote in message ... Bob, Sorry for taking so long to respond I have been working on a rush project. Two considerations... Since I cannot spell VBA and the spreadsheet is maintained by my clerical staff, I am looking for a simpler solution. I have looked at the conditional formatting that I am currently using and I can handle another way. If I use conditional formatting can I get 6 colors, I would like 8 but I can manager with 6, and how do I shade the entire row. Thanks for your help. -- JPS "Bob Phillips" wrote: You need VBA. '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "DW:DW" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "L": .EntireRow.Interior.ColorIndex = 48 '40% grey Case "F": .EntireRow.Interior.ColorIndex = 5 'blue 'etc Case Else: .EntireRow.Interior.ColorIndex = xlColorIndexNone End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "JPS" wrote in message ... I have a large spreadsheet and based on a status indicator in Column DW, I need to shade the entire row one of eight colors, For example, if a cell value in row DW is L the whole row needs to be shaded gray. Another example. is if DW is F, the row needs to be Blue. If DW is blank, the row is not shaded. Is this doable with IF statements; I already use Conditional Formatting for another purpose. I am using Excel 2003 -- JPS |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shading Rows With IF Statements?
In 2003 you are limited to 3 conditions..........4 if you count default.
You are going to have to learn to spell VBA Gord Dibben MS Excel MVP On Tue, 14 Oct 2008 23:02:04 -0700, JPS wrote: Bob, Sorry for taking so long to respond I have been working on a rush project. Two considerations... Since I cannot spell VBA and the spreadsheet is maintained by my clerical staff, I am looking for a simpler solution. I have looked at the conditional formatting that I am currently using and I can handle another way. If I use conditional formatting can I get 6 colors, I would like 8 but I can manager with 6, and how do I shade the entire row. Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shading rows | Excel Worksheet Functions | |||
Shading of rows | Excel Worksheet Functions | |||
shading data in rows | Excel Discussion (Misc queries) | |||
shading rows | Excel Discussion (Misc queries) | |||
shading different rows when a value changes in col. | Excel Worksheet Functions |