Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace conditional formatting to use 4 conditions
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace conditional formatting to use 4 conditions
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 -- Regards Frank Kabel Frankfurt, Germany Rob wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace conditional formatting to use 4 conditions
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... "Rob" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace conditional formatting to use 4 conditions
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 -----Original Message----- 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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting for 4 conditions | Excel Worksheet Functions | |||
Conditional Formatting - more than 3 conditions | Excel Discussion (Misc queries) | |||
More conditions for Conditional Formatting? | Excel Discussion (Misc queries) | |||
Need 6 Conditions / Conditional Formatting | Excel Worksheet Functions | |||
more than 3 conditions in conditional formatting - possible? | Excel Discussion (Misc queries) |