Excel 2010 - Conditional formatting, color adjacent cells in same row

Gianluca picture Gianluca · Nov 8, 2013 · Viewed 37k times · Source

Is is possible in Excel 2010 to set a conditional formatting rule to highlight all not empty cells in a row when the cell in particular column has a particular value?

I have a report in which every row identify a day. I would like to colour in grey the rows relative to Saturday and Sunday. The day is stored in column C.

I know how to highlight cells in column C, but how can I easily extent the format of cell C to the adjacent not empty cells in the same row?

Answer

barry houdini picture barry houdini · Nov 8, 2013

This is easy to do without a macro and without using INDIRECT function

Assuming you have data starting at row 2 and that the "day" in column C is a text value then do this:

Select whole range of data, e.g. A2:J100

apply in conditional formatting the formula that needs to apply to the first row, e.g.

=AND($C2="Saturday",A2<>"")

That will apply formatting to all cells in the range if col C of that row is "Saturday" and the cell itself is not blank. Note C2 needs a $ in front because it applies to C for the whole row A2 doesn't need $

If you want to apply to Saturday and Sunday the same type of formatting then use an OR, i.e.

=AND(OR($C2="Saturday",$C2="Sunday"),A2<>"")

....or if the column C entries are actual dates make that

=AND(WEEKDAY($C2,2)>5,$C2<>"",A2<>"")

See example workbook with that last CF formula demonstrated