Conditional Formatting, colour entire row based upon one cell - Help please

Hi there,

I have a training spreadsheet with perhaps a dozen columns on it. Each row has a series of text or numbers but the key columns are the dates in columns 5, 8, 10 & 12.

I need to colour each row from column 1 to 6 based upon the date field in column 5 and then columns 7 & 8 based upon the date in 8, 9 & 10 based upon the date in 10 and 11 & 12 based upon the date in 12. I have created a conditional format that colours column 5, but how do I make it colour columns 1 to 6 based upon the column 5 date please?

Any assistance gratefully received.

Cheers,

Muke

If you used a formula, then use Absolute Referencing to "freeze" the column 5 reference. e.g. =$E2

Make sure the "Applies To" reference shows a range covering the 6 columns you want to highlight.

Hi and thanks for the quick response. I have the cell range in the applies to, but my date cell goes the correct colour (red), while the other 'text' cells all go green.


I'm using the 'Format only cells that contain option' which I'm guessing is incorrect, but I can't see an alternative option that lets me identify column E as the key cell, but choose the range to colour also.

Am I sort of in the right place or am I really missing something?

Cheers,

Marcus

You should use the "use a formula to determine which cells to format" option.

Perhaps you can post a sample workbook here, we can help you better then.

View attachment Contractors Induction Master List22.xlsxThanks again for the response. I tried that, but now I'm having further problems with the highlighting.

I have attached the workbook and the sheet I'm working on is on the 'Active' tab. The date in Column E, is the one that I need to use to colour columns A to F. As you will see from the key in the top left hand corner, there are three criteria that I need to use : Anything that has passed in red, anything in the next month (30 days) in Amber and anything after that in green.

I really appreciate your help with this.

Cheers,

Marcus

Try these:

Green: =$E6>TODAY()+30
Red: =$E6<TODAY()
Yellow: =AND($E6>=TODAY(),$E6<=TODAY()+30)

That has worked a treat, thank-you so much. I've copied the concept of the formulas into the other columns, changing the column letter and everything appears to be just what we were looking for.

Thanks again for your time and help with this.

Cheers,

Muke