#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    159
    Rep Power
    31

    Excel Conditional Formatting Question


    I have a spreadsheet with the following conditional format rules (as formulas):
    Code:
    =(INDIRECT("G"&ROW())="Cancelled")     //Applies to $A:$G and Turns Row Red
    =(INDIRECT("G"&ROW())="Hold")          //Applies to $A:$G and Turns Row Amber
    =(INDIRECT("G"&ROW())="Complete")      //Applies to $A:$G and Turns Row Green
    These work fine if the cell in Column G matches Cancelled, Hold or Complete, but I would like to change it so that if any cell in column G contains (not matches) those keywords anywhere in that cell that the whole row changes color. I have tried many variations of find/search/iserror with no luck. Please help
    Last edited by Nullified; January 10th, 2012 at 01:43 PM.
  2. #2
  3. Automagically Delicious
    Devshed Regular (2000 - 2499 posts)

    Join Date
    May 2004
    Location
    127.0.0.2 - I live next door.
    Posts
    2,202
    Rep Power
    2737
    Which version of Excel and are you using VBA script or just cell formulas?
    Adam TT
  4. #3
  5. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    I could easily tell you how to do this with VBA, but as far as doing it strictly with a worksheet formula I don't have any ideas.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    159
    Rep Power
    31
    I am using Excel 2007. I was using just formulas, but I didn't even think about using visual basic. I guess I could do that, if you could give me a heads up on a proper macro function.
  8. #5
  9. Automagically Delicious
    Devshed Regular (2000 - 2499 posts)

    Join Date
    May 2004
    Location
    127.0.0.2 - I live next door.
    Posts
    2,202
    Rep Power
    2737
    Code:
    Sub ColorRow()
    
         If range("G3").value = find("hold") Then
            range("A3:F3").cells.interior.colorindex = 3
        Else if range("g3").value = find("complete") Then
            range("A3:F3").cells.interior.colorindex = 4
        End If
    End Sub
    Here is a great page for detailing the colors of the backgrounds and text and how to do it. Otherwise you can do something as simple as what I have or throw it into a loop. Google will be your best friend for many of those commands.
    Adam TT

IMN logo majestic logo threadwatch logo seochat tools logo