Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Coloring (Color Coding) (2000)

    Hello Loungers,

    I am attempting to explain an accrual to some non-accounting people. I have put together some "dummy numbers" to do so which reflect accruals and reversals.

    I would like to be able to color code the thing to help those folks better understand it. Is it possible, without manually doing so, to have all cells with a value originating in a particular cell "filled" with the same color? For example, if the accrual is in cell A1, but that value (=a1) is in cells B2, C5 and E6, I would like to have all of these filled in with light blue.

    Is this possible via formulas, etc?

    Thanks.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Coloring (Color Coding) (2000)

    You could put some code like this in the worksheet change event routine in the module behind the worksheet:

    <pre>Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    For Each oCell In Target
    If oCell.HasFormula Then
    If UCase(oCell.Formula) = "=A1" Then
    oCell.Interior.ColorIndex = 8
    Else
    oCell.Interior.ColorIndex = xlColorIndexNone
    End If
    Else
    oCell.Interior.ColorIndex = xlColorIndexNone
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Coloring (Color Coding) (2000)

    Another possibility is to run this macro:

    Sub ColorDependents()
    Dim oCell As Range
    On Error GoTo ExitHandler
    For Each oCell In ActiveCell.Dependents.Cells
    oCell.Interior.ColorIndex = 34
    Next oCell
    ExitHandler:
    End Sub

    Less dynamic, but it handles more complex formulas.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell Coloring (Color Coding) (2000)

    <P ID="edit" class=small>(Edited by Rudi on 22-Apr-05 16:02. OK, I think I missed the point. You are wanting dependant cells from A1 highlighted!!! (ie. cells linked to a formula in A1)...sorry!)</P>See the attachment. By using conditional formatting with a formula like: =C1=$A$1
    Regards,
    Rudi

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Coloring (Color Coding) (2000)

    How do I "put some code like this in the worksheet change event routine in the module behind the worksheet" and then execute it? Never done this before.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell Coloring (Color Coding) (2000)

    Copy the code of Legare.
    Open the W/B
    Right Click on the worksheet tab and choose View Code
    Paste the code into the blank module.

    PS: If it pastes into one long line, delete it and paste the code into word first, recopy the code from word and then paste it into the module!
    Regards,
    Rudi

  7. #7
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Coloring (Color Coding) (2000)

    Thanks. I made it through your steps. How then do I run the module?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Coloring (Color Coding) (2000)

    The code Legare provided is a worksheet event procedure, i.e. it reacts to changes in your workbook. If you change the formula of a cell to =A1, it should automatically be colored.

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Coloring (Color Coding) (2000)

    You can run the code in one of two ways. You can "step through it" or click on the "run sub/userform" icon located below the "Debug" dropdown.

    To step through it, simply place your cursor on the section of code you want to run and press F8. Each time you press F8 you will be excecuting the next line of code. The step process is especially good for debuging.

    Hope this helps,
    John

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Coloring (Color Coding) (2000)

    Actually, neither of those will work with an event routine. You have to trigger the event that the event routine is for. In this case, you do that by changing any cell on the worksheet.
    Legare Coleman

Posting Permissions

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