+ Reply to Thread
Results 1 to 10 of 10

Auto color-shading of rows

  1. #1
    Fitz
    Guest

    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. #2
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Tushar Mehta
    Guest

    Re: Auto color-shading of rows

    In article <[email protected]>, 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. #4
    Fitz
    Guest

    Re: Auto color-shading of rows

    I appreciate the feedback but I don't understand this code at all. In
    reality, I'm looking for 4 different color scenarios: if "424" is written in
    a cell in a specific column of an excel worksheet 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. Will this
    macro work on each worksheet in the workbook? Appreciate your feedback.
    Fitz



  5. #5
    Bob Phillips
    Guest

    Re: Auto color-shading of rows

    It will with a slight modification. This will work on any change in column M
    (13)

    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


    'This is workbook event code.
    'To input this code, right click on the Excel icon on the worksheet
    '(or next to the File menu if you maximise your workbooks),
    'select View Code from the menu, and paste the code

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Fitz" <[email protected]> wrote in message
    news:[email protected]...
    > I appreciate the feedback but I don't understand this code at all. In
    > reality, I'm looking for 4 different color scenarios: if "424" is written

    in
    > a cell in a specific column of an excel worksheet 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. Will this
    > macro work on each worksheet in the workbook? Appreciate your feedback.
    > Fitz
    >
    >




  6. #6
    Fitz
    Guest

    Re: 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



  7. #7
    Bob Phillips
    Guest

    Re: Auto color-shading of rows


    "Fitz" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  8. #8
    Fitz
    Guest

    Re: 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.



  9. #9
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >
    >




  10. #10
    Adrian
    Guest

    RE: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1