Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello all,

    I'm trying to set up conditional formatting to color the entire row based on the information in a range of cells. I can get it to work based on one cell, but am having trouble with more than one cell reference. Here is what I can get to work:

    =INDIRECT("A"&ROW())=$AL$4


    The information in cell AL4 is a date, and I have various dates in the cell range of AL4:AL30. I need it to color the row if the date equals ANY of the dates in the range. The dates are always changing, and they are not consecutive so I can't use a static date range.

    Any help is much appreciated.

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The value in AL4 will ALWAYS match at least one of the values in AL4:AL30, namely the one in AL4 (obviously). I assume that's not what you intended. So please be more precise.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I apologize for not being more clear.

    The cell that contains the date is in column A, and if the date matches any of the dates in AL4:AL30, I want the row colored a specific color.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Select the rows (or the entire sheet). Let's say that the active cell is in row 1.
    Use the following formula in the Conditional Formatting dialog:

    =NOT(ISERROR(MATCH($A1,$AL$4:$AL$30,0)))

    Because the column is fixed in $A1, conditional formatting will look at column A even if the cell being formatted is in another column.
    Because the row is relative in $A1, it will be adjusted automatically for the other rows.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry to continue asking questions. I'm either doing this wrong or I've not explained what I'm looking for.

    I am using Excel 2007 if that makes any difference.

    I've pasted the formula you provided in the Rule Description part of the dialog. I do not receive an error, but no cells are formatting.

    In 2007, it allows you to choose the area where you want the formatting to take place in a different step.

    I have over 2300 rows and this will continue to increase each day as I add more data.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I hope that someone who has Excel 2007 will be able to help you.

Posting Permissions

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