microsoft excel training - Conditional Formatting

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

Resolved · Low Priority · Version Standard

ReplyWed 22 Nov 2006, 11:13Claire (guest) said...

Conditional Formatting

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 (£)		List price
Excel VBA Intro Intermediate 2 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice	List price
2007	Mon 9 + Tue 10 Aug 2010	Bloomsbury	0	FULL			£495
2003	Wed 11 + Thu 12 Aug 2010	Southwark	3		£495	£495	£495
2003	Wed 25 + Thu 26 Aug 2010	Bloomsbury	8		£450	£455	£495
2007	Thu 2 + Fri 3 Sep 2010	Bloomsbury	5		£490	£495	£495
2003	Wed 8 + Thu 9 Sep 2010	Southwark	8		£450	£455	£495
2003	Tue 21 + Wed 22 Sep 2010	Southwark	8		£450	£455	£495
Full Schedule: See all 47 Excel VBA Intro Intermediate course dates. Bookings currently available until 28th September 2011.

835 posts

ReplyWed 22 Nov 2006, 11:35Trainer Richard said...

RE: Conditional Formatting

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

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

· Business Forecasting With Microsoft Excel
· How to Create Better Excel Spreadsheets: Part Three
· A Hidden Gem In Excel - Formula Auditing
· Microsoft Office and the Small Business: Part 1 Excel
· Why Excel Training (Sometimes) Doesn't Work and What You Can Do About It

Excel tip:

Display Functions on Worksheets

Functions in Excel can be difficult to recall their format/syntax

For example, you want to use the =PMT function.

Enter =PMT, then select keystroke, CTRL+SHIFT+A.

This usful memory jog, will display the arguments of a function on a worksheet, allowing the user to proceed with the generation of the function

View all Excel hints and tips