Colour Coding

[fiaz]

Hi
I have a list of 20 items in a table with codes, configurations, pallet types and lane capacity, i'm using the vlookup formula to insert the codes etc. when i select the items. However i would like to have the items colour coded automatically when i select them. How do i do this?

 

[GTO]

Greetings Fiaz,

This is not complete, as I'm not for certain as to what you mean by selecting "items". Presuming you mean cells in a given range, maybe start with this...

In a throwaway copy of your workbook:

In a Standard Module:

Option Explicit
Public rngActiveCell As Range

In ThisWorkbook Module:

Option Explicit
Private Sub Workbook_Open()
    '// Change to sheetname that has table//
    If ActiveSheet.Name = "Sheet1" Then
        Set rngActiveCell = ActiveCell
    End If
End Sub

In the sheet's module:

Option Explicit
Private Sub Worksheet_Activate()
    Set rngActiveCell = ActiveCell
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    '//  Change range to table range//
    If Not Application.Intersect(Target, Range("A1:B20")) Is Nothing _
    And Not Target.Count > 1 Then
        
        Target.Interior.Color = &HFF8080
        rngActiveCell.Interior.ColorIndex = xlColorIndexNone
        Set rngActiveCell = Target
    End If
End Sub

As stated, this is not complete, as it doesn't "clean up" after itself, save rngActiveCell between runtimes, etc. Just an example to see if this is in the area you are trying to head.

Hope this helps,

Mark

 

[Greg Truby]

Welcome to the board. Your question is a bit vague. Please clarify. Also, please indicate the version of Excel that you're using. If you're using Excel 2007, it might be possible to solve your problem with some conditional formatting. Again, we need to better understand what it is that you're trying to do.