Change cell colour by week

[xlqs]

Hi, I am trying to figure out how to change the cell colour by what week it is.
So in the top row I have the week number (atm just a number, no special formating) and I would like that cell to change colour as the week goes by. I've tried a few different formulas with little success (week 25, 26, 27 in the picture).

Grateful for any help and tips!

 

[Snakehips]

@xlqs. Welcome.
Something like below?

MrExcel July.xlsm
	B	C	D	E	F	G	H	I
1
2		25	26	27	28	29
3
4
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:I2	Expression	=AND(C2>0,C2<=WEEKNUM(TODAY(),1))	text	NO

 

[xlqs]

@xlqs. Welcome.
Something like below?

MrExcel July.xlsm
	B	C	D	E	F	G	H	I
1
2		25	26	27	28	29
3
4
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:I2	Expression	=AND(C2>0,C2<=WEEKNUM(TODAY(),1))	text	NO

I tried that formula but with no effect. Do I need to lable the weeks as dates? I've tried looking in to this too, but not sure how I do that without needing to add the actual date of each week.

 

[Snakehips]

That exact formula, or suitably edited to match the first cell of your CF applies to range?

 

[xlqs]

That exact formula, or suitably edited to match the first cell of your CF applies to range?

edited to match my range. Do I need to write the formula in Swedish if the excel is in swedish? Or do english formulas work also? I tried posting this question in swedish but was told by the website I'm not allowed to do that since I asked it in english already.

 

[Snakehips]

I honestly don't know what impact would be if you are using Swedish Excel.
I do not see any reason why equivalent 'Swedish' formulas should not exist and work.
I assume that at least, your regional delimiter will be different, ie not a comma?
The numbers in my example are just 25, 26 27... in General format.

 

[Anthony47]

Hi, I am trying to figure out how to change the cell colour by what week it is.
So in the top row I have the week number (atm just a number, no special formating) and I would like that cell to change colour as the week goes by. I've tried a few different formulas with little success (week 25, 26, 27 in the picture).

From that statement I am not able to understand which cells should change color and in which condition.
Could you attach an image with more conditions an more colors and explain in some more details?

 

[Fluff]

Do I need to write the formula in Swedish if the excel is in swedish?

Yes you will which is

=OCH(C2>0;C2<=VECKONR(IDAG();1))

 

[xlqs]

Thank you! This works!

 

[Fluff]

Glad we could help & thanks for the feedback.