Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Auto color-shading of rows
Can I automate an excel worksheet such that if ,say, "436" is written in a
certain cell it shades that row yellow, or if "437" is in that cell it shades the row blue. It would then be easy to visually tell in an excel chart which lines belonged to ,say, specific companies. |
#2
|
|||
|
|||
Auto color-shading of rows
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" 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 436: .EntireRow.Interior.ColorIndex = 6 Case 437: .EntireRow.Interior.ColorIndex = 5 'etc 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 RP (remove nothere from the email address if mailing direct) "Fitz" wrote in message .. . Can I automate an excel worksheet such that if ,say, "436" is written in a certain cell it shades that row yellow, or if "437" is in that cell it shades the row blue. It would then be easy to visually tell in an excel chart which lines belonged to ,say, specific companies. |
#3
|
|||
|
|||
Auto color-shading of rows
In article , fitz1
@sympatico.ca says... Can I automate an excel worksheet such that if ,say, "436" is written in a certain cell it shades that row yellow, or if "437" is in that cell it shades the row blue. It would then be easy to visually tell in an excel chart which lines belonged to ,say, specific companies. In addition to a programmatic solution, you may also want to check out conditional formatting (Format | Conditional Formatting...) -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
#4
|
|||
|
|||
Auto color-shading of rows
Doesn't seem to be working. Can't explain. I'm working in Excel 2003. If
I right click the worksheet thats relevant to the request (it's also the one maximized), I get the option to "View Code". If I then left-click "View Code", I end up in Microsoft Visual Basic and I have a window for sheet 1 which contains two drop-down boxes: The left one has (General) and Worksheet as the 2 dropdown choices and the right one has 10 choices including selectionchange and activate, to name two. Where specifically should I paste this and does it effect only that sheet or all other sheets as well in that workbook. I only want it to effect one worksheet in the workbook. I also don't want it to be run as regular code in other workbooks every time I open excel. In any case, I then copy and paste the code (as shown below) you've provided in its entirety and press enter. When I then get back into Excel it questions me about adjusting my security settings to allow a macro to run. I do this but it still does not work. No hightlighting of lines as desired. Any more advice or ideas would be greatly appreciated. Please be specific as I'm in over my head when it comes to code and Microsoft VBA. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Target.Column = 13 Then With Target Select Case .Value Case 424: .EntireRow.Interior.ColorIndex = 6 Case 426: .EntireRow.Interior.ColorIndex = 35 Case 436: .EntireRow.Interior.ColorIndex = 41 Case "TAU": .EntireRow.Interior.ColorIndex = 45 'etc End Select End With End If ws_exit: Application.EnableEvents = True End Sub |
#5
|
|||
|
|||
Auto color-shading of rows
"Fitz" wrote in message . .. Doesn't seem to be working. Can't explain. I'm working in Excel 2003. If I right click the worksheet thats relevant to the request (it's also the one maximized), I get the option to "View Code". If I then left-click "View Code", I end up in Microsoft Visual Basic and I have a window for sheet 1 which contains two drop-down boxes: The left one has (General) and Worksheet as the 2 dropdown choices and the right one has 10 choices including selectionchange and activate, to name two. Where specifically should I paste this Just paste the code into the big blank area of the window that shows. The dropdowns can be ignored here. and does it effect only that sheet or all other sheets as well in that workbook. I only want it to effect one worksheet in the workbook. I also don't want it to be run as regular code in other workbooks every time I open excel. I gave you code for a single sheet first time, then for all sheets after your follow-up question. If you want just one sheet, you have the wrong code, use this version instead Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A1" 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 436: .EntireRow.Interior.ColorIndex = 6 Case 437: .EntireRow.Interior.ColorIndex = 5 'etc End Select End With End If ws_exit: Application.EnableEvents = True End Sub |
#6
|
|||
|
|||
Auto color-shading of rows
Just to get this all back on track, because I copied and pasted wrong code,
do I now have to delete any code that I have in there already? If so, where do I delete?Secondly, on the one sheet that I'm interested in getting the rows to be highlighted (tabbed "CC130"), the column of interest is column F. In column F there will be four possible entries as outlined below: -if "424" is written in a cell in column F then the row, in its entirety, associated with that "424"cell will be highlighted in YELLOW - if "426" is written in that same column in any particular cell then the complete row associated with that cell would be hightlighted in LIGHT GREEN, -if "436" is written in that same column in any particular cell then the complete row associated with that cell would be hightlighted in LIGHT BLUE, -if "TAU" is written in that same column in any particular cell then the row associated with that cell would be hightlighted in LIGHT ORANGE. I only need this highlighting to work on this one particular worksheet in the workbook. When I insert any code it appears to be viewing this as a macro and Excel wants me to enable macros. How do I tell excel that this macro is written by a trusted publisher so it will run behind the scenes everytime I open this file without asking me to enable macros all over again? Please just restate the exact code required for this scenario because the latest does not seem to address a specific column (ie. F or 6) nor does it include two of the four possible cell input possibilities listed above. Thanks for your patience. |
#7
|
|||
|
|||
Auto color-shading of rows
Fitz,
Remove this code Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Target.Column = 13 Then With Target Select Case .Value Case 424: .EntireRow.Interior.ColorIndex = 6 Case 426: .EntireRow.Interior.ColorIndex = 35 Case 436: .EntireRow.Interior.ColorIndex = 41 Case "TAU": .EntireRow.Interior.ColorIndex = 45 'etc End Select End With End If ws_exit: Application.EnableEvents = True End Sub replace it with this code Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 6 Then Select Case .Value Case 436: .EntireRow.Interior.ColorIndex = 6 Case 437: .EntireRow.Interior.ColorIndex = 5 'etc End Select End If End With ws_exit: Application.EnableEvents = True End Sub The only way you can avoid the message is to set your security setting to low, which I don't advise, or digitally sign it. I haven't done this, but it is described at Note that certification only works in Excel 2000 and later. http://msdn.microsoft.com/library/de.../odc_dsvba.asp Code Signing Office XP Visual Basic for Applications Macro Projects http://msdn.microsoft.com/library/de...tml/combat.asp Combat Macro Viruses with Digital Signatures Another reference: http://msdn.microsoft.com/library/en...vbaproject.asp http://office.microsoft.com/assistan...ustworthy.aspx How to Tell if Digital Certificate Is Trustworthy in Office XP -- HTH RP (remove nothere from the email address if mailing direct) "Fitz" wrote in message .. . Just to get this all back on track, because I copied and pasted wrong code, do I now have to delete any code that I have in there already? If so, where do I delete?Secondly, on the one sheet that I'm interested in getting the rows to be highlighted (tabbed "CC130"), the column of interest is column F. In column F there will be four possible entries as outlined below: -if "424" is written in a cell in column F then the row, in its entirety, associated with that "424"cell will be highlighted in YELLOW - if "426" is written in that same column in any particular cell then the complete row associated with that cell would be hightlighted in LIGHT GREEN, -if "436" is written in that same column in any particular cell then the complete row associated with that cell would be hightlighted in LIGHT BLUE, -if "TAU" is written in that same column in any particular cell then the row associated with that cell would be hightlighted in LIGHT ORANGE. I only need this highlighting to work on this one particular worksheet in the workbook. When I insert any code it appears to be viewing this as a macro and Excel wants me to enable macros. How do I tell excel that this macro is written by a trusted publisher so it will run behind the scenes everytime I open this file without asking me to enable macros all over again? Please just restate the exact code required for this scenario because the latest does not seem to address a specific column (ie. F or 6) nor does it include two of the four possible cell input possibilities listed above. Thanks for your patience. |
#8
Posted to microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto color-shading of rows
Try using conditional formatting under the format menu
"Fitz" wrote: Can I automate an excel worksheet such that if ,say, "436" is written in a certain cell it shades that row yellow, or if "437" is in that cell it shades the row blue. It would then be easy to visually tell in an excel chart which lines belonged to ,say, specific companies. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to have formulas and formats auto copy to new inserted rows | Excel Discussion (Misc queries) | |||
auto deleting defined rows | Excel Discussion (Misc queries) | |||
Auto Insert Rows of Data?? | Excel Discussion (Misc queries) | |||
Column Auto Width and Hidden Rows | Excel Discussion (Misc queries) | |||
Auto adjusting # rows between 2 worksheets | New Users to Excel |