microsoft excel training - Conditional Formatting

The UK's most regular instructor-led training courses.
Training information: microsoft excel training · Excel/courses/london · Microsoft Excel Training London
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Low Priority · Version Standard

Conditional Formatting

by - Claire [guest] (2006 Nov 22 Wed, 11:13) Reply

Is there a way to extend the range of conditional formatting to more than three conditions?

Version	Date	Location	Places available	Book	Next place rate:
Excel VBA 2 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003/XP	2008 Jul 17 Thu + 18 Fri	Bloomsbury	3	book now	£475	£495
2003/XP	2008 Jul 30 Wed + 31 Thu	Bayswater	5	book now	£450	£485
2003/XP	2008 Aug 7 Thu + 8 Fri	Bloomsbury	6	book now	£450	£485
2003/XP	2008 Aug 14 Thu + 15 Fri	Bloomsbury	5	book now	£450	£485
2003/XP	2008 Aug 26 Tue + 27 Wed	Bayswater	6	book now	£450	£485
2003/XP	2008 Sep 8 Mon + 9 Tue	Bayswater	7	book now	£345	£399
Full Schedule: See all 48 Excel VBA course dates. Bookings currently available until 26th November 2009.

RE: Conditional Formatting

by - trainer Richard platinum contributer [761 posts] (2006 Nov 22 Wed, 11:35) Reply

Yes, you can do it by using VBA coding. If you have not had any experience using VBA, this may be a steep learning curve.

STEPS:

Save your workbook, then activate the worksheet, right-click its Sheet Name tab, select View Code, and enter the following code:

----------------------

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

---------------