<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Conditional Formatting

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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 IfEnd 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 gohere 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. ALLpurchases 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

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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