Solved: conditional formatting VBA

[julesnash]

I am trying to create a golf scorecard that conditionally formats the colour of my score based on the "par score" for any particular hole.

(see attached pic) C19:T19 contains the "par score", and I enter my scores on C3:T3 through to C17:T17 (and beyond when I play more rounds).

I need the following formatting:

If my score = (par score - 1) then format cell blue
If my score = (par score) then format cell green
If my score = (par score + 1) then format cell yellow
If my score = (par score + 2) then format cell orange
If my score = (par score + 3) then format cell red

Thank you in advance of your help!

[p45cal]

Which version of Excel?

[julesnash]

2003

Thanks

[julesnash]

Oh, I should also change the first and the last conditions as follows, as there is a chance I could hit better than a birdie, or worse than a triple bogie (but I am sure you would have thought of this already!)

If my score <= (par score - 1) then format cell blue
If my score >= (par score + 3) then format cell red

[p45cal]

You've got more than the number of conditions that conditional formatting in Excel 2003 can cope with so it has to be a macro solution.
Right click the tab of the sheet and choose View Code…, then paste the following code where there's a blinking text cursor. Close the newly opened application.
Test by changing values in the Par row and where you put the scores.
Save the macro by saving the workbook.[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Set ScoresRange = Range("C3:T17")
ParScoresRow = 19
Set xxx = Intersect(Union(Intersect(Columns("C:T"), Rows(ParScoresRow)), ScoresRange), Target)
If Not xxx Is Nothing Then
For Each cll In ScoresRange.Cells
Select Case UCase(cll.Value)
Case "NR": cll.Interior.ColorIndex = 48 'grey
Case "", 0: cll.Interior.ColorIndex = xlNone 'no fill
Case -10 To 100
YourScore = cll.Value
Par = Cells(ParScoresRow, cll.Column).Value
Difference = YourScore - Par
Select Case Difference
Case Is < 0: cll.Interior.ColorIndex = 5 'blue
Case 0: cll.Interior.ColorIndex = 4 'green
Case 1: cll.Interior.ColorIndex = 6 'yellow
Case 2: cll.Interior.ColorIndex = 45 'orange
Case Is > 2: cll.Interior.ColorIndex = 3 'red
End Select
Case Else: cll.Interior.ColorIndex = xlNone 'no fill
End Select
Next cll
End If
End Sub
[/vba]It works by detecting an update to the values in any cell either in the Par values or where you enter the scores, and once detected will process all the cells in the range where you enter scores.
There will be a problem when you add rows. You can see from the code that I've used 19 for the Par row (ParScoresRow = 19), and for the range where you enter the scores (ScoresRange = Range("C3:T17")).
As soon as you want to add more rows you'll have to move something.
We can make these ranges dynamic, but we have to know the rules (logic) about where they are to be found.

[julesnash]

Sir, you are a genius, with obviously far too much time on your hands :P

It works perfectly, once I had realised that I had to remove the conditional formatting which I had tried using with only 3 conditions. I guess the conditional formatting overrules the VBA?

[p45cal]

Sir, you are a genius, with obviously far too much time on your hands :P

It works perfectly, once I had realised that I had to remove the conditional formatting which I had tried using with only 3 conditions. I guess the conditional formatting overrules the VBA?

Conditional formatting overrules conflicting formatting, regardless of how it got there, vba or not. All the vba does is the same as you would if you coloured the cells yourself.