Conditioning

Of course, we are not talking about strength training for your body.  What we are talking about today is conditional formatting where you apply a format (i.e. cell color, bold font, etc) based on what the cell contains.

The easiest (and highly effective) method of conditional formats is through the built in function provided in Excel.  You select a series of cells then apply the conditions the where a cell in that range will receive that formatting.  In Excel 2010 you have the added luxury of shades of colors to demonstrate where the value falls within that range of cells (among your many choices).   This adds great value when you are dealing with large data sets and you wish to visually demonstrate the values.  When many formulas exist with varying degrees of complexity (maybe even feeding one into the next) you can use this to alter your variables to visualize the affect of those changes throughout the rest of your sheet.

Let’s say you want further flexibility in your formats?  For instance, you may want the user to define what range to use or you need to limit the file size for ease in sending.  You may want to identify your conditions through a code like that found in my last post:

---

Sub TrueFormat()

    With Selection

        .Font.Bold = True

        .Font.TintAndShade = 0.349986266670736

        .Interior.Color = 65535

        .Borders(xlEdgeBottom).LineStyle = xlContinuous

    End With

End Sub

---

You can now reference this code within a set of conditions in VBA as follows:

---

Sub TrueFormat()

    With Selection

        .Font.Bold = True

        .Font.TintAndShade = 0.349986266670736

        .Interior.Color = 65535

        .Borders(xlEdgeBottom).LineStyle = xlContinuous

    End With

End Sub

Sub FindTrue()

    For I = 1 To 100

‘if the value is more than 1 apply the true format to that cell

        If Cells(I, 2) > 1 Then

            Application.Run ("TrueFormat")

‘if the value is more than 1 apply another format to that cell

        ElseIf Cells(I, 2) < -100 Then

            Application.Run ("AnotherFormat")

.

.

.

        End If

    Next I

End Sub

---

The result is a conditionally formatted sheet without the excessive file size!  In addition, you can add as many notes as you like to your code with a leading apostrophe to help you follow the (sometimes larger than life) series of if/then statements.  Remember, it may make perfect sense to you now but if you have to revisit it years from now these notes will bring you up to speed far faster than without.