View original (French)

Conditional formatting entire column by week number

Solved
duaner16759 Posted messages 2 Status Member -  
duaner16759 Posted messages 2 Status Member -
Hello,

I am seeking your help for a problem I cannot solve.

In an Excel workbook that serves as my annual planner, where each column corresponds to a week number, I want the entire column of the week matching today's date to be red, for example.

Thank you in advance for your help!

Configuration: Windows 7 / Firefox 15.0.1

3 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello

    assuming your range from A1 to X1000 to adapt, with the week numbers in row 1 starting from A1
    Select A1:X1000
    Conditional formatting by formula
    The formula is:
    =A$1=WEEKNUM(TODAY())
    pay attention to the $ sign in the right place
    Format as you wish and OK OK
    Note: depending on the years, Excel's weeks (American year) are offset compared to ours. *Correct with WEEKNUM(TODAY())+1 or -1 as needed. For 2012, the calendars are in agreement.

    best regards

    --
    aren't we alone in understanding what is being explained?
    4
    1. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      Hello Vaucluse,

      Sorry for encroaching on your turf with forms ;-)

      With the 2 explanations from duaner16759, he should manage his color!

      Enjoy your drink and have a good day.
      0
  2. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello,

    For your MFC, use a formula like: 

    =WEEKNUM(TODAY(),2)=A$1

    - With a range from column A to column AZ
    - the first week's column in A
    - the week number in row 1
    it's up to you to adjust the range and the formula to your workbook.
    --

    Always zen
    Perfection is achieved not when there is nothing more to add, but rather when there is nothing more to take away. Antoine de Saint-Exupéry
    1
  3. duaner16759 Posted messages 2 Status Member
     
    Perfect, thank you both and enjoy your meal.
    0