Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Conditional Formatting

Get Around Excels 3 Criteria Limit in Conditional Formatting

Back to: Excel VBA . Got any Excel/VBA Questions? Free Excel Help

SEE ALSO: Non VBA Method Conditional Format for Fonts

Excel has a very useful feature in Excel named Conditional Formatting . It can be found via Format>Conditional Formatting on the Worksheet Menu Bar. The feature allows us to format a cell based on its content. For example, if the cell is greater than 5, but less then 10, we may have the cell change to a red background. We can do this for up to 3 conditions only, which sometimes is not enough. If we want more than 3 conditions we can use Excel VBA code that is automatically fired whenever a user makes any change in a specified range. Let's assume we want to have 6 separate conditions in the range A1:A10 on a particular Worksheet. To do so try this. Activate the Worksheet then right click on its name tab and select "View Code". In here put the code as shown below. See here for details on Select Case Statement which can accommodate text & numbers.

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

Now click the top right X, or use Alt+Q, to get back to your Worksheet. Enter some numbers in range A1:A10 between 1 to 3 and the background color of each cell will change. The color used is determined by the passing of a number to the variable icolor, which in turn passes this number to Target.Interior.ColorIndex The number that is passed is determined by the Case x To x For example, if we enter the number 22 in any cell within the range A1:A10 the number 15 is passed to icolor and then icolor (now having value of 15) is passed to the Target.Interior.ColorIndex making the cell grey. Target is always the cell that has changed and thus fired the code.

The only problem this leaves us with is what colors are represented by what numbers. We can obtain our needed number by recording a macro changing a cells background color, or you can go here for a Custom Function that will return a cells color by its ColorIndex number or by name

SEE ALSO: Non VBA Method Conditional Format

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft