CONDITIONAL FORMATTING (More than 3 Criteria's)

CONDITIONAL FORMATTING (More than 3 Criteria’s)

Use & Explanation:

The steps below will show you how to do more than 3 conditional formattings in an excel sheet, which would otherwise allow you only 3 conditional formattings at any one time.

The Cell colours will automatically change depending on the colour you have chosen for the Text or number you have specified as the criteria.

STEPS IN DETAIL

STEP

• On a New Excel Sheet, go to Tools, then go to Macro, then go to Visual Basic Editor. (Or you can also simply skip the step above and press the Alt key with the F11 key to open up the Visual Basic Editor)

STEP 2

• In the new screen that you see, Right click on the Sheet1, click on Insert, then click on Module.

STEP 3 1,2

• In Module1, copy paste the code given below, into the blank screen on the right side of the screen, just as shown in the snapshot.

• Now click on Sheet1 on the left side of the screen.

• Copy paste the code given below, here aswell.

• Save the sheet and close the entire sheet completely.

Note: You can change all the data highlighted in red, in the code below, depending on what numbers or what text, you would like to format in your excel sheet.

For Example:

• • To change the cell colour for the text ABCD & for the numbers in the range of 1 to 3 to a colour of your choice, simply change the number mentioned in this portion of the code (oCell.Interior.ColorIndex = 3) which currently corresponds to the colour red (see Color Table below)

• To change the Font size of the number 10 to Normal font, change the word True to False in this portion of the code (oCell.Font.Bold = False)

• You can also delete the Text or Number criteria's you don’t require, such as ABCD and numbers 1 to 3 for that you'll need to delete these 3 lines of the code, like wise for any other criteria's as well :

oCell.Interior.ColorIndex = 3

oCell.Font.Bold = True

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oCell As Range

For Each oCell In Target

Select Case oCell.Value

Case 1 To 3, "ABCD"

oCell.Interior.ColorIndex = 3

oCell.Font.Bold = True

Case Is = 4, "EFGH"

oCell.Interior.ColorIndex = 4

oCell.Font.Bold = True

Case 5 To 9, "IJKL"

oCell.Interior.ColorIndex = 5

oCell.Font.Bold = True

Case Is = 10

oCell.Interior.ColorIndex = 6

oCell.Font.Bold = True

Case Else

oCell.Interior.ColorIndex = xlNone

End Select

Next oCell

End Sub

The Colour Table

A completed Conditional Formatting will look like this: