Results 1 to 2 of 2
2004-02-10, 12:34 #1
- Join Date
- Oct 2001
- Suffolk, England
- Thanked 0 Times in 0 Posts
Conditional Formatting (Excel 2000)
I want to apply 5 different conditional formats to a range of cells. (Excel will only allow 3 in the conditional formatting) At the moment I have overcome this problem by a macro and a 'Select Case', but then I have to physically run the macro. If there anyway I can run this macro whenever the cells are updated, without having to press shortcut keys or press a button etc?
2004-02-10, 12:45 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Conditional Formatting (Excel 2000)
You can use the Worksheet_Change event. This event occurs whenever the user changes a value in a cell.
<UL><LI>Activate the Visual Basic Editor.
<LI>Make sure that the worksheet containing the cells is visible in the Project Explorer.
<LI>Double click the worksheet node in the Project Explorer to open the corresponding code module.
<LI>Type code like the following (you can copy and paste this code, then adapt it to your needs):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
' Substitute appropriate range
If Not Intersect(Target, Range("A1:C3")) Is Nothing Then
For Each oCell In Intersect(Target, Range("A1:C3"))
' Modify as needed
Select Case oCell.Value
Case Is < 0
oCell.Interior.ColorIndex = 2
oCell.Interior.ColorIndex = 3
Case Is < 5
oCell.Interior.ColorIndex = 4
Case Is < 10
oCell.Interior.ColorIndex = 5
oCell.Interior.ColorIndex = 6
Set oCell = Nothing
<LI>Switch back to Excel[/list]You can also use a combination of conditional formatting and custom number formatting, but that is less flexible.