Results 1 to 7 of 7

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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.


  3. #3
    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

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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.


  5. #5
    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

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Try these:

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


  7. #7
    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

Posting Permissions

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