Highlight only sunday of current week

[papil]

i am looking for way to highlight only sunday of current week. every time i open the excel it should only highlight that way.
can someone help with the same. thanks. data looks like below-
[TABLE="width: 174"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]week[/TD]
[TD]date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2/11/2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2/18/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2/25/2018[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3/4/2018[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3/11/2018[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3/18/2018[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3/25/2018[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4/1/2018[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4/8/2018[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4/15/2018[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4/22/2018[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]4/29/2018[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]5/6/2018[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]5/13/2018[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]5/20/2018[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]5/27/2018[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]6/3/2018[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]6/10/2018[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]6/17/2018[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]6/24/2018[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]7/1/2018[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]7/8/2018[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]7/15/2018[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]7/22/2018[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]7/29/2018[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]8/5/2018[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]8/12/2018[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]8/19/2018[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]8/26/2018[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]9/2/2018[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]9/9/2018[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]9/16/2018[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]9/23/2018[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]9/30/2018[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]10/7/2018[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]10/14/2018[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]10/21/2018[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]10/28/2018[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]11/4/2018[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]11/11/2018[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]11/18/2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

So it looks like all your dates are already only Sundays. So all we need to do is check to see if the week number of the date matches the week number of the current date.
We can do that pretty easily with Conditional Formatting.

Assuming your dates are in column B, select column B, and apply this Conditional Formatting formula:

=weeknum(B1)=weeknum(today())

and choose your desired highlighting color

 

[JustynaMK]

Hi papil,

Assuming that your dates are in Sheet1, column A (starting from row 1), you can start by defining a new Name, e.g. "rngSunday"
=IFERROR(MATCH(TODAY(),Sheet1!$A:$A,0),MATCH(TODAY(),Sheet1!$A:$A,1)+1)

It will return the correct row number for the exact date (if "today" is Sunday), or the approximate date (if "today" is not Sunday).

Select the whole column (e.g. column A) and go to Conditional Formatting > New Rule > Use a foruma to determine which cells to format. Enter the following formula:
=ROW(A1)=rngSunday

Let me know if that works for you.

 

[Joe4]

JustynaMK,

That seems like overkill. Did you see my solution above?

Even if they did have a situation where they have non-Sunday dates in their list, that could be handled with a minor change to my CF formula, i.e.

=AND(WEEKDAY(B1)=1,WEEKNUM(B1)=WEEKNUM(TODAY()))

 

[papil]

=AND(WEEKDAY(B1)=1,WEEKNUM(B1)=WEEKNUM(TODAY())) this is not working for me.

 

[Jon von der Heyden]

Or, assuming dates in B2:B42:

Highlight the range, starting at B2.
Go to conditional formatting > Use a formula to determine which cells to format
Formula: =B2=CEILING(TODAY()-1,7)+1
Click Format > choose desired format
Hit OK

 

[papil]

Or, assuming dates in B2:B42:

Highlight the range, starting at B2.
Go to conditional formatting > Use a formula to determine which cells to format
Formula: =B2=CEILING(TODAY()-1,7)+1
Click Format > choose desired format
Hit OK


This works .Thanks

 

[Joe4]

=AND(WEEKDAY(B1)=1,WEEKNUM(B1)=WEEKNUM(TODAY())) this is not working for me.

If your data truly looks like you posted in your original post, I do not see why it wouldn't work.
Did you highlight the whole column, or just specific rows in the column?

Note that when applying Conditional Formatting to multiple cells at once, you need to write the CF formula as it pertains to the very first cell in the range you have selected, and Excel will automatically adjust it for all other cells. I instructed to select the WHOLE column B, so cell B1 would be the first cell in that selection (doesn't matter if there is a header in row 1, it won't meet the CF criteria). Jon's solution tells you to select B2:B42, so his formula references B2 (the first cell in his selection).