Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autocomplete in color (2002 SP-2)

    I like the way Excel autocompletes an entry in a cell. Saves me a lot of trouble when I'm tryping the same handful of account names into a worksheet over and over again whenever I post my transactions. In one sheet I have separate accounts identified by different color so that I can identify them quickly at a glance. Unfortuantely, I don't know how to get Excel to autocomplete an entry in a cell in a certain color font. Is this possible? Ideally, all the cells in a row would have the same color as the entry in cell B, which is where the account name goes.
    Clark

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

    Re: Autocomplete in color (2002 SP-2)

    You can use the Worksheet_Change event.
    - Activate the Visual Basic Editor (Alt+F11)
    - Double click the relevant worksheet in the Project Explorer.
    - Copy the following code into the module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim intIndex As Integer
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("B:B")).Cells
    Select Case oCell.Value
    Case "ABC Inc"
    intIndex = 3
    Case "Bronco"
    intIndex = 4
    Case "Chrono Systems"
    intIndex = 5
    Case Else
    intIndex = 1
    End Select
    oCell.EntireRow.Font.ColorIndex = intIndex
    Next oCell
    End If
    Set oCell = Nothing
    End Sub

    Substitute the appropriate names, and add extra cases as needed.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autocomplete in color (2002 SP-2)

    I'm assuming intIndex=3, etc. refers to colors. How do I know which number refers to which color?
    Clark

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

    Re: Autocomplete in color (2002 SP-2)

    Hans' code will work (it almost always does), however it requires that the code be modified every time a new company is added. The code below will color a new entry in column A the same as the first cell in the column with the same value. If you add a new company the color is set to the default color. You can then color the new company and it will use that color from then on.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range, I As Long, lMax As Long
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    For Each oCell In Intersect(Target, Range("A:A"))
    If oCell.Value = "" Then
    oCell.Interior.ColorIndex = xlColorIndexNone
    Else
    For I = 0 To Range("A65536").End(xlUp).Row - 1
    If I <> (oCell.Row - 1) Then
    If Trim(UCase(oCell.Value)) = Trim(UCase(Range("A1").Offset(I, 0).Value)) Then
    oCell.Interior.ColorIndex = Range("A1").Offset(I, 0).Interior.ColorIndex
    Exit For
    End If
    End If
    Next I
    End If
    Next oCell
    End Sub
    </pre>


    I have attached a file that shows the code working.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autocomplete in color (2002 SP-2)

    So, would I put this in the module for the specific worksheet, or as a module under the workbook in general?
    Clark

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

    Re: Autocomplete in color (2002 SP-2)

    It must go in the worksheet module. Right click on the Sheet1 tab in the workbook I attached to my first message and select "View Code" from the popup menu and you will see where it goes.
    Legare Coleman

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

    Re: Autocomplete in color (2002 SP-2)

    Thanks, that is much better than my code.

Posting Permissions

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