Conditional Formatting using multiple cell references

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

I hope that someone who has Excel 2007 will be able to help you.

Reply | Quote