colorindex problem

Topic

New Reply

Good morning

I have a Control Form Combo box on several worksheets. From the Combo I can select an upper or lower case lettter and the active cell is coloured accordingly and this works fine. I now wish to add an action for ‘L’ and ‘l’ and have amended the code accordingly (please see below) and I have also changed the Combobox Listfillrange to accomodate the extra entries.

My problem is that if I select the ‘L’ or the ‘l’ from the Combobox it inserts the letter OK but does not change the font or cell colour. I have tried experimenting with different colours but it just does not work, any ideas please

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    For Each cel In Range("C6:AG1227").Cells
        If IsError(cel.Value) Then
            ' do you want to colour these?
        Else
            Select Case UCase(cel.Value)
                Case "H", "h"
                    cel.Font.ColorIndex = 2
                    cel.Interior.ColorIndex = 3
                Case "S", "s"
                    cel.Font.ColorIndex = 2
                    cel.Interior.ColorIndex = 51
                Case "M"
                    cel.Font.ColorIndex = 2
                    cel.Interior.ColorIndex = 26
                Case "P"
                    cel.Font.ColorIndex = 2
                    cel.Interior.ColorIndex = 9
                Case "U", "u"
                    cel.Font.ColorIndex = 2
                    cel.Interior.ColorIndex = 1
                Case "A", "a"
                    cel.Font.ColorIndex = 2
                    cel.Interior.ColorIndex = 16
                Case "B", "b2"
                    cel.Font.ColorIndex = 2
                    cel.Interior.ColorIndex = 13
                Case "T", "t"
                    cel.Font.ColorIndex = 2
                    cel.Interior.ColorIndex = 25
                 Case "C", "c"
                    cel.Font.ColorIndex = 2
                    cel.Interior.ColorIndex = 49
                 Case "L", "l"
                    cel.Font.ColorIndex = 2
                    cel.Interior.ColorIndex = 46
                Case "X", "x"
                    cel.Font.ColorIndex = 2
                    cel.Interior.ColorIndex = 2
Case Else
                    cel.Font.ColorIndex = 1
            End Select
            End If
    Next cel
End Sub

Reply | Quote

Replies

Selecting an item from the combobox does NOT trigger the “Change Event”, so the code will not run after you use the combo box. You would have to manually change a cell to make the code run this way.

What you could do is to copy the code to a general module and give it a name. Then you could have the worksheet change event call this macro, which will then work as you have it now. Then you can right-click the combo box and assign the macro to it so it runs when the combo box is called.

Steve

Reply | Quote

Selecting an item from the combobox does NOT trigger the “Change Event”, so the code will not run after you use the combo box. You would have to manually change a cell to make the code run this way.

What you could do is to copy the code to a general module and give it a name. Then you could have the worksheet change event call this macro, which will then work as you have it now. Then you can right-click the combo box and assign the macro to it so it runs when the combo box is called.

Steve

Hi Steve

Thanks for the input. I have been using this for a couple of years and the way it works is that a user double clicks the relevant cell and that makes the ComboBox visible, the user then selects one of the letters from the Combo selection and the cell that has been double clicked is populated by that letter and the cell background colour changes and the letter is inserted in white (you can of course just type in the lettter but I use the Combo as it makes the user beleive that is the only way it works and then then can only select a lettter from the Combo)

All I wanted to do was to add a couple of days as the Company has introduced Lieu days when people have accrued enough hours and this is working OK insofar as the Combo selection of ‘L’ or ‘l’ is placed into the cell but the background colour is not working but all of the others do. I can live without it being coloured in but was just trying to understand why everything else worked and this did not.

Reply | Quote

By the way, since you’re checking UCase(cel.Value), there’s no point in including lower case letters – UCase(cel.Value) will never be a lower case letter. Instead of

Case “H”, “h”

it is sufficient to use

Case “H”

and similar for the other letters.

Steve has already explained that you need to use the code differently. As a Worksheet_Change event procedure it is horribly inefficient, since the code will work through each and every cell in the range C6:AG1227 whenever the user edits ANY cell in the workbook.

Reply | Quote

By the way, since you’re checking UCase(cel.Value), there’s no point in including lower case letters – UCase(cel.Value) will never be a lower case letter. Instead of

Case “H”, “h”

it is sufficient to use

Case “H”

and similar for the other letters.

Steve has already explained that you need to use the code differently. As a Worksheet_Change event procedure it is horribly inefficient, since the code will work through each and every cell in the range C6:AG1227 whenever the user edits ANY cell in the workbook.

Hi Hans

If for example the user has selected H his personal summary on a seperate page updates and deducts 1 day from his holiday allocation, and if h is selected the same thing happens but it deducts 1/2 a day. If I change this to case ‘H’ would that still work?

I will copy the worksheet and try to change the ‘Worksheet_Change’ event procedure but in all honesty there never seems to be any delay and I am adminstering 5 countries, 12 departments and 90 people. expect a barrage of questions as I change and play about with it.

Thanks as usual

Reply | Quote

If for example the user has selected H his personal summary on a seperate page updates and deducts 1 day from his holiday allocation, and if h is selected the same thing happens but it deducts 1/2 a day. If I change this to case ‘H’ would that still work?

The Select Case in the code that you posted is only used to change the colouring of the cell, it has nothing to do with the calculation of holiday deductions.

Since you have Select Case UCase(cel.Value), the code will NEVER have to check “h” or “s” or “u” etc. UCase converts the value of the cell to upper case.

Reply | Quote

I can’t see anything different about the code for L, so are you sure that it’s actually just L and not for example L with a leading or trailing space?

Reply | Quote