Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    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
    Case 0
    oCell.Interior.ColorIndex = 3
    Case Is < 5
    oCell.Interior.ColorIndex = 4
    Case Is < 10
    oCell.Interior.ColorIndex = 5
    Case Else
    oCell.Interior.ColorIndex = 6
    End Select
    Next oCell
    End If
    Set oCell = Nothing
    End Sub

    <LI>Switch back to Excel[/list]You can also use a combination of conditional formatting and custom number formatting, but that is less flexible.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •