Replace conditional formatting to use 4 conditions

[Rob]

I have a table as so:

A B C D E F G
1 Candidate Basic Car Fee % Fee 50/50 Month
2 Don Wood £32000 £5000 17.5% £6475 FALSE 3
3 Ron Kinn £44500 £5000 17.5% £8663 FALSE 3
4 Rob Simms £35000 £5000 17.5% £7000 FALSE 3
5 Jay Atkins £16000 £6000 17.5% £3850 FALSE 2
6 Ed Barry £31000 £5000 15.5% £5580 FALSE 2
7 Ron Pass £33000 £5000 17.5% £6650 FALSE 2
8 Adie Carthy £42000 £4500 12.5% £5813 FALSE 1
9 Don Carter £43000 £2000 20.0% £9000 FALSE 1

I have a 3 conditional formats that cover the whole table, relating to
the month colum saying
1) formulais: =IF($A9<>"",$G9=1) then colour whole column blue
2) formulais: =IF($A9<>"",$G9=2) then colour whole column red
3) formulais: =IF($A9<>"",$G9=3) then colour whole column green

This is all fine and dandy until I have a month 4 to contend with.....

I have a feeling I need to use some sort of Worksheet_Change VBA type
cleverness. Which is beyond me.

Can anyone help? Please?

 

[Frank Kabel]

Hi
as an example the following will color the entry in cell A1:A100 based
on its value:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Red": .Interior.ColorIndex = 3
Case "Blue": .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

 

[Guest]

I don't want to take you in a direction that you are not comfortable with but
I see your design as being problematic, not to mention that you are going to
end up with a christmas tree effect with all of that conditional formatting.
Try taking the raw data that you have and placing it in a pivot table. You
data looks ideal for that purpose and it will be a much more flexible design
in the end.

Creating the pivot table...

Click anywhere in the data you have -> Choose Data -> Pivot Table

Choose Finish When the wizard pops up (99% of the time this will work great)

Drag the months and names to the right column. Drag the numbers to the
middle. Drag the 50/50 to the top row... Or something like that. Add an auto
format and "Bobs Your Uncle". Play with it. It can be kind of fun...

 

[Guest]

I'm hoping when you said colour whole column blue, you
meant row instead of column. If I read you correctly, you
CAN use a Worksheet Change event based on changes in
Column G. One way is this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 And Len(Target.Value) > 0 Then
ColorRange = Target.Offset(0, -6).Address & ":" &
Target.Address
Select Case Target.Value
Case 1
ActiveSheet.Range(ColorRange).Interior.ColorIndex = 5
Case 2
ActiveSheet.Range(ColorRange).Interior.ColorIndex = 3
Case 3
ActiveSheet.Range(ColorRange).Interior.ColorIndex = 10
Case 4
ActiveSheet.Range(ColorRange).Interior.ColorIndex = 4
Case Else
ActiveSheet.Range(ColorRange).Interior.ColorIndex =
xlNone
End Select
End If
End Sub

This uses a Case structure to choose a row color based on
the number in the Month row. You can fill in the rest of
the 8 month colors.

tod