Using VBA to Extend Excel 291

File Operations 293

Combining and Separating Workbooks____299

Working with Cell Comments 303

Utilities to Wow Your Clients 308

Techniques for VBA Pros 313

Cool Application 327

Next Steps 330

The control sheet consists of two columns: Column A holds the variables being watched, and Column B holds the number conforming to the desired color. After the control sheet is configured, the variables can be entered on the main sheet and colored accordingly:

Private Sub Worksheet_Change(ByVal Target As Range) ' Conditional Formatting for more than 3 conditions

Dim rng As Range

1 Target is a range: therefore, it can be more than one cell ' For example, someone could delete the contents of a range, 1 or someone could enter an array. Set rng = Intersect(Target, Range("D:D")) If rng Is Nothing Then Exit Sub


Dim cl As Range

For Each cl In rng

On Error Resume Next

1 — The preceding line doesn't change the cell's background ' — color if the cell's value is not found in the range ' — that we specified (rngcolors). cl.Interior.Colorlndex = _

Application.WorksheetFunction.VLookup(cl.Value, _ ThisWorkbook.Sheets("Sheet3").Range("rngColors"), _ 2, False) If Err.Number <> 0 Then cl.Interior.Colorlndex = xlNone End If Next cl End If

End Sub

0 0

Post a comment