Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Orlando, Florida, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF Statements/Conditional Formatting (2003)

    I have an issue where I want to color code rows based on individual information entered into a range of cells.

    In the attached example I will have serial numbers in column C and then numbers from 1-5 are going to be entered into the D:M cells. What I want it to do is to look for the lowest number in any of the D:M cells and then color code the cells in that row from C:M with the color associated with the lowest number in the range. For example in row 11, 2 is the lowest number entered into the D11:M11 range so I would want C11:M11 to have a background color of Orange, based on the color chart in cells E33:E43.

    This seems to me like it should be easy to do with If statements and/or Conditional Formatting but I am having difficulties figuring it out.

    Suggestions?

    Thanksl
    Jill

  2. #2
    New Lounger
    Join Date
    Feb 2003
    Location
    Orlando, Florida, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Statements/Conditional Formatting (2003)

    Forgot to post the file. Here it is.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF Statements/Conditional Formatting (2003)

    Conditional formatting doesn't help here since it allows only 3 conditions. You can use the Worksheet_Change event:
    - Right-click the sheet tab for Sheet1.
    - Select View Code from the popup menu.
    - Copy the following code into the module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim lngRow As Long
    Dim lngRealRow As Long
    Dim lngMin As Long
    Set rng = Intersect(Target, Range("D11:M30"))
    If Not rng Is Nothing Then
    For lngRow = 1 To rng.Rows.Count
    lngRealRow = rng.Rows(lngRow).Row
    lngMin = WorksheetFunction.Min(Range("D" & lngRealRow & ":M" & lngRealRow))
    Range("B" & lngRealRow & ":M" & lngRealRow).Interior.ColorIndex = _
    Range("E32").Offset(2 * lngMin, 0).Interior.ColorIndex
    Next lngRow
    End If
    Set rng = Nothing
    End Sub

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Location
    Orlando, Florida, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Statements/Conditional Formatting (2003)

    Works perfectly!

    Thanks!
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

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