Excel 03 - Macro help with conditional formatting

_Andy_

Hi

Using Excel 2003, I have a sheet which dynamically lists teams/team members names and their hours (using a drop down at top).

Each team has a 'colour' we would normally use to identify them.

I want, where the team name appears, the team colour to be the background colour for that cell.

As there are more than three teams I can't use conditional formatting.

I understand this is a VBA/macro job - after Googling for a while I got the following, but this doesn't seem to change existing data (i.e. if I enter, say 'team 1' in an empty cell, the background colour changes, whereas the other references to 'team 1' that were already there, don't.

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

If Not Intersect(Target, Range("A1:B100")) Is Nothing Then
Select Case Target
Case "team 1"
icolor = 10
Case "team 2"
icolor = 12
Case "team 3"
icolor = 7
Case "team 4"
icolor = 53
Case "team 5"
icolor = 15
Case "team 6"
icolor = 42
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub"


Any ideas?
thanks

Stompa

I'd guess the macro is only being triggered when the value in a cell changes. Try cutting and pasting all your existing cells containing 'team 1' etc. elsewhere then cut and paste them back to their original locations. Alternatively, simply selecting them all, pressing delete and choosing undo may be sufficient.

_Andy_

Thanks - I think you're right about the trigger. My VBA knowledge is pitiful so not sure of a way around it.
I need the formatting to be automated either fully (the user does nothing) or at least just has to press a button that would trigger a macro that would format everything in the sheet.
One issue is that the contents (and therefore the references to teams) in the sheet will/can change.

cheers

Stompa

Does it matter? As long as the all cells in your original spreadsheet are the correct colour any subsequent changes should trigger the macro and everything should be fine.

_Andy_

I've tried doing it that way and even if the cell subsequently changes (e.g. the user changes team on the dropdown), the background colour won't shift.
E.g. the dropdown / datavalidation cell does change colour (to reflect the team name chosen), but cells that change in response to that (to the same team name) won't change colour.
Very frustrating!
thanks

TheBaptist

_Andy_ wrote: »

I've tried doing it that way and even if the cell subsequently changes (e.g. the user changes team on the dropdown), the background colour won't shift.
E.g. the dropdown / datavalidation cell does change colour (to reflect the team name chosen), but cells that change in response to that (to the same team name) won't change colour.
Very frustrating!
thanks

The reason it won't work for the other cells is for the reason pointed out - you aren't changing them or even selecting them. The macro is only checking that the cell currently changing is within the range and if so, it will run.

You could always change the macro so that if there's any change in the range that all cells are analysed, for example create a loop to check all cells.

_Andy_

TheBaptist wrote: »

The reason it won't work for the other cells is for the reason pointed out - you aren't changing them or even selecting them. The macro is only checking that the cell currently changing is within the range and if so, it will run.

You could always change the macro so that if there's any change in the range that all cells are analysed, for example create a loop to check all cells.

many thanks
Could you give me some guidance to make the loop to check all cells?

Stompa

I'm not a VBA programmer, so the following code is rather crude but appears to work:

Public Sub SetTeamColour(ByRef Target As Range)
    Dim icolor As Integer
    Select Case Target
        Case "team 1"
            icolor = 10
        Case "team 2"
            icolor = 12
        Case "team 3"
            icolor = 7
        Case "team 4"
            icolor = 53
        Case "team 5"
            icolor = 15
        Case "team 6"
            icolor = 42
        Case Else
            'Whatever
    End Select
    Target.Interior.ColorIndex = icolor
End Sub
 
Private Sub Worksheet_Calculate()
    Dim oCell As Range
    For Each oCell In Range("A1:B100") 'range of cells containing formulae
      SetTeamColour oCell
    Next oCell
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B100")) Is Nothing Then 'range of cells containing values
        SetTeamColour Target
    End If
End Sub

there are probably much better ways to do this though.

_Andy_

awesome, thanks Stompa, much appreciated. I'll try that in the morning

_Andy_

Hm, just trying it, get 'Run time error 13' - when I debug it's highlighting the first 'case..' (I've changed the teams to their actual names e.g. 'Communications')

Any ideas?

thanks

Stompa

_Andy_ wrote: »

Hm, just trying it, get 'Run time error 13' - when I debug it's highlighting the first 'case..' (I've changed the teams to their actual names e.g. 'Communications')

Any ideas?

thanks

It's a type mismatch error, i.e. the code is finding a number where it expected a string (or perhaps vice versa). I'd guess you might need to ensure that all the relevant cells are formatted as Text.

You might also try changing:

Select Case Target

to:

Select Case Target.Text

I did warn you I wasn't a VBA expert!