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 | I
- 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 :
Case 1 To 3, "ABCD" 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 | 1 | 53 | 52 | 51 | 49 | 55 | 56 | 9 | 46 | 12 | 10 | 5 | 47 | 16 | 3 | 45 | 43 | 50 | 41 | 13 | 48 | 7 | 44 | 6 | 4 | 33 | 54 | 15 | 38 | 40 | 36 | 35 | 37 | 39 | 2 |
| A completed Conditional Formatting will look like
this: |
| A | B | C | D | E | F | G | 1 | ABCD | 1 | 2 | 3 | | | | 2 | EFGH | 7 | | | | | | 3 | IJKL | 5 | 6 | 7 | 8 | 9 | | 4 | 10 | | | | | | | 5 | | | | | | | | 6 | | | | | | | | 7 | | | | | | | | 8 | | | | | | | |
| 
