microsoft excel training - conditional formatting

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » microsoft excel training - Conditional Formatting

microsoft excel training - Conditional Formatting

resolvedResolved · Low Priority · Version Standard

Conditional Formatting


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

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

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


 

Excel tip:

Sorting List Subtotals

If you find that you would prefer to show the items in a subtotalled list in a different order, eg ascending rather than descending, you can sort your list. To sort a subtotalled list, hide the detail rows and then sort the subtotal rows. When you sort a subtotalled list, the hidden detail rows are automatically moved with the subtotal rows.
IMPORTANT: If you do not hide the details rows before sorting a subtotalled list, your subtotals will be removed and all of the rows in your list will be reordered.

View all Excel hints and tips


Server loaded in 0.73 secs.