Conditional formatting > 3 variables

[hugo15]

In Excel 2003 I need to use conditional formatting but I've got more than 3 variables. I've done a search and found the code I need but I can't make the changes to get it to run in my speadsheet.

I'm trying to get it to work for Sheet 1 in the range C3 to Z33 and I want different colours for the cell values P, GP, PS and MD.

Would someone be so kind as to amend the code below that I've been trying to use to get it to work for my range and variables.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("A1:A10")) is Nothing Then

Select Case Target

Case 1 To 5
icolor = 6

Case 6 To 10
icolor = 12

Case 11 To 15
icolor = 7

Case 16 To 20
icolor = 53

Case 21 To 25
icolor = 15

Case 26 To 30
icolor = 42

Case Else
'Whatever

End Select

Target.Interior.ColorIndex = icolor

End If

End Sub

Thanks!!

 

[dcardno]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor          As Integer

If Not Intersect(Target, Range("C3:Z33")) Is Nothing Then

    Select Case Target.Value

        Case "P"
            icolor = 6

        Case "GP"
            icolor = 12

        Case "PS"
            icolor = 7

        Case "MD"
            icolor = 53

        Case Else
            'Whatever

    End Select

    Target.Interior.ColorIndex = icolor

End If

End Sub

You may want to play around with the colour selections - I find them a bit jarring; but that's just a matter of taste.

 

[hugo15]

Thanks. I'll give it a go when I'm back at work tomorrow.

Does it matter that there are other worksheets in the same file when you name the range?

 

[dcardno]

No - this code has to go in the 'worksheet module' for that worksheet (actually a class module for the worksheet, if I have my terminology right) - the module will "watch" for a change event in the worksheet it is attached to - changes in other worksheets won't trigger any response.

 

[hugo15]

Many thanks for your help Dean, I've managed to get it working now. I was adding Sheet1 to my range and I guess that is why it didn't work.

The only slight snag is that it only seems to work on a single cell. For example, if I want to copy "P" from cell C3 paste into the range C4:C6 then the code falls over. If I hold down ctrl and select cells C4, C5 and C6 then it seems to work. Any idea how I can modify the code to get it to work so I can paste in a range?

 

[dcardno]

Hugo - there is no easy way to change the code so it will respond to a paste - for whatever reason, Excel does not consider a paste operation to be a 'change' event in a worksheet.

The only workarounds I can think of would be to change the code so that it was triggered on -say- a calculation event (or before save, etc). Currently on every change the code checks whether the change applies to a cell within the defined range C3:Z33; if it has it tests for the specific string values in the cell and colours the cell appropriately. If the code were to run on a calculation it would have to test every cell in that range for the values of interest, and then colour the cells. It's not a huge range, but this will exact some small performance penalty.

This would work...

Private Sub Worksheet_Calculate()
Dim icolor          As Integer
Dim cl              As Range
For Each cl In Range("C3:Z33")
    Select Case cl.Value
        Case "P"
            icolor = 6
        Case "GP"
            icolor = 12
        Case "PS"
            icolor = 7
        Case "MD"
            icolor = 53
        Case Else
            icolor = 0
    End Select
    cl.Interior.ColorIndex = icolor

Next cl

End Sub

But then you have to trigger a calculation - if the spreadsheet is just a list, an entry into a cell won't do it. If you have a calculation like a subtotal depending on which of the values {P, GP, PS, MD} are entered in this range, then that subtotal will recalculate on every entry, and that will test and colour (or re-colour) the appropriate cells. It may even be worth inserting that sort of a dummy function to force a calculation on every entry, depending on how big and complicated the sheet is.

Alternatively, you could take roughly the same code (it would be the same except without the "Worksheet_Calculate" or other trigger), put it in a regular module, and attach it to a command button. You would lose the immediate colour change (whcih may be important) but you would be able to force an examination (and colouring) of all cells in the range.