Underline the last of each cell with conditional formatting

D

DarkoDeign2

Board Regular
Joined
Jun 20, 2023
Messages
76
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a column $AI2:$AI1500 in this column I have ISO-week numbers (e.g. wk22)
In the next column $AJ2:$AJ1500 I have days of the week (e.g. Monday... Sunday). It can be multiple rows with the same days.

I want to a conditional format the last Sunday in every week in column AI.
What suggestions do you have for such a formula?
1689351291738.png
 

Attachments

  • 1689351181610.png
    1689351181610.png
    7.4 KB · Views: 7
Sort by date Sort by votes
Hi,

I have been using the suggested formula for a while now and it works great. The issue is that some weeks I don't have any values for Sundays.
So those weeks this doesn't work.
How should I go about if instead of basing the criteria to find the last Sunday for every week, i would like to update the criteria to search for the last instance for each week.
Any help appreciated.

Peter_SSs said:
:oops: Didn't I just have incorrect sample data (I used my wrong sample sheet with the original CF)?
The formula did give the total for the week, it just wasn't in the last row of the week because of my dodgy data.


The highlighted Sunday supposed to be the last day of that week shouldn't it?
If so, same formula, better sample data.

DarkoDeign2.xlsm
AKALARAT
1Week noDays
2wk28Friday1 
3wk28Friday2 
4wk28Saturday3 
5wk28Sunday410
6wk29Sunday5 
7wk29Sunday6 
8wk29Sunday718
9wk30Monday8 
10wk30Monday9 
11wk30Tuesday10 
12wk30Tuesday11 
13wk30Friday12 
14wk30Saturday13 
15wk30Saturday14 
16wk30Sunday15 
17wk30Sunday16 
18wk30Sunday17125
19wk31Monday18 
20wk31Monday19 
21wk31Monday20 
22wk31Thursday21 
23wk31Friday22 
24wk31Saturday23 
25wk31Sunday24 
26wk31Sunday25 
27wk31Sunday26198
28wk32Monday27 
29wk32Tuesday28 
30wk32Wednesday29 
CF Last (2)
Cell Formulas
RangeFormula
AT2:AT30AT2=IF(AND($AL2="Sunday",$AK3&$AL3<>$AK2&$AL2),SUMIF($AK$2:$AK$1500,$AK2,$AR$2:$AR$1500),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AK2:AL30Expression=AND($AL2="Sunday",$AK3&$AL3<>$AK2&$AL2)textNO
Click to expand...
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
  1. Can you confirm that you are talking about the suggestion in post #12, not the marked solution?
  2. Are you asking about a change to the Conditional Formatting or the formulas in column AT or both?

What about a new small set of sample data and the expected results with XL2BB?
 
Last edited:
Upvote 0
Hi,

Sorry if i was a bit unclear, yes I am talking about post #12.
As you can see below since week 31 and week 33 don't have any instances for Sundays, the conditional formatting is not working.
I would like the last instance for each week to have conditional formatting. So in the example below I would like the row 142, 156, 173, 192 to be conditionally formatted.
I hope this explains what I am after a bit better.

Book1
AKALAMAN
133wk31Monday3129
134wk31Monday3130
135wk31Wednesday3131
136wk31Wednesday3132
137wk31Thursday3133
138wk31Thursday3134
139wk31Friday3135
140wk31Friday4135
141wk31Friday4136
142wk31Saturday4137
143wk32Monday4138
144wk32Monday4139
145wk32Tuesday4140
146wk32Wednesday4141
147wk32Wednesday4142
148wk32Wednesday4143
149wk32Thursday4144
150wk32Thursday4145
151wk32Thursday4146
152wk32Friday4147
153wk32Saturday5147
154wk32Saturday5148
155wk32Sunday5149
156wk32Sunday5150
157wk33Monday5151
158wk33Monday5152
159wk33Monday5153
160wk33Tuesday6153
161wk33Tuesday6154
162wk33Wednesday6155
163wk33Wednesday6156
164wk33Wednesday7156
165wk33Wednesday7157
166wk33Wednesday8157
167wk33Wednesday8158
168wk33Thursday8159
169wk33Friday8160
170wk33Friday8161
171wk33Friday8162
172wk33Friday8163
173wk33Saturday8164
174wk34Monday9164
175wk34Monday9165
176wk34Tuesday9166
177wk34Wednesday9167
178wk34Wednesday10167
179wk34Wednesday10168
180wk34Wednesday10169
181wk34Thursday10170
182wk34Thursday10171
183wk34Friday10172
184wk34Friday10173
185wk34Friday10174
186wk34Friday10175
187wk34Saturday11175
188wk34Saturday12175
189wk34Saturday13175
190wk34Sunday13176
191wk34Sunday13177
192wk34Sunday13178
Tracking
Cell Formulas
RangeFormula
AK133:AK192AK133=IF(ISBLANK($A133),"",ISOWEEKNUM($A133))
AL133:AL192AL133=IF(ISBLANK($A133),"",TEXT($A133, "[$-0809]dddd"))
AM133:AM192AM133=IF(NOT(ISBLANK($A133)),COUNTIF($AG$2:$AG133,"Ongoing"),"")
AN133:AN192AN133=IF(NOT(ISBLANK($A133)),COUNTIF($AG$2:$AG133,"Closed"),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AI2:AU1504Expression=AND($AL2="Sunday";$AK3&$AL3<>$AK2&$AL2)textNO
AM2:AP1504Expression=AND(ISBLANK($A3); NOT(ISBLANK($AG2)))textNO

Book1
AKALAMAN
1Week noDaysOngoingClosed
Tracking
 
Upvote 0
Thanks. Try this then

23 09 11.xlsm
AKAL
133wk31Monday
134wk31Monday
135wk31Wednesday
136wk31Wednesday
137wk31Thursday
138wk31Thursday
139wk31Friday
140wk31Friday
141wk31Friday
142wk31Saturday
143wk32Monday
144wk32Monday
145wk32Tuesday
146wk32Wednesday
147wk32Wednesday
148wk32Wednesday
149wk32Thursday
150wk32Thursday
151wk32Thursday
152wk32Friday
153wk32Saturday
154wk32Saturday
155wk32Sunday
156wk32Sunday
157wk33Monday
158wk33Monday
159wk33Monday
160wk33Tuesday
161wk33Tuesday
162wk33Wednesday
163wk33Wednesday
164wk33Wednesday
165wk33Wednesday
166wk33Wednesday
167wk33Wednesday
168wk33Thursday
169wk33Friday
170wk33Friday
171wk33Friday
172wk33Friday
173wk33Saturday
174wk34Monday
175wk34Monday
176wk34Tuesday
177wk34Wednesday
178wk34Wednesday
179wk34Wednesday
180wk34Wednesday
181wk34Thursday
182wk34Thursday
183wk34Friday
184wk34Friday
185wk34Friday
186wk34Friday
187wk34Saturday
188wk34Saturday
189wk34Saturday
190wk34Sunday
191wk34Sunday
192wk34Sunday
193
CF Last
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AK133:AL192Expression=$AK133<>$AK134textNO
 
Upvote 0
Solution
This doesn't seem to work for me...
All the cells/rows gets formatted.

Book1
AKALAMAN
1Week noDaysOngoingClosed
Tracking

Book1
AKALAMAN
133wk31Monday3129
134wk31Monday3130
135wk31Wednesday3131
136wk31Wednesday3132
137wk31Thursday3133
138wk31Thursday3134
139wk31Friday3135
140wk31Friday4135
141wk31Friday4136
142wk31Saturday4137
143wk32Monday4138
144wk32Monday4139
145wk32Tuesday4140
146wk32Wednesday4141
147wk32Wednesday4142
148wk32Wednesday4143
149wk32Thursday4144
150wk32Thursday4145
151wk32Thursday4146
152wk32Friday4147
153wk32Saturday5147
154wk32Saturday5148
155wk32Sunday5149
156wk32Sunday5150
157wk33Monday5151
158wk33Monday5152
159wk33Monday5153
160wk33Tuesday6153
161wk33Tuesday6154
162wk33Wednesday6155
163wk33Wednesday6156
164wk33Wednesday7156
165wk33Wednesday7157
166wk33Wednesday8157
167wk33Wednesday8158
168wk33Thursday8159
169wk33Friday8160
170wk33Friday8161
171wk33Friday8162
172wk33Friday8163
173wk33Saturday8164
174wk34Monday9164
175wk34Monday9165
176wk34Tuesday9166
177wk34Wednesday9167
178wk34Wednesday10167
179wk34Wednesday10168
180wk34Wednesday10169
181wk34Thursday10170
182wk34Thursday10171
183wk34Friday10172
184wk34Friday10173
185wk34Friday10174
186wk34Friday10175
187wk34Saturday11175
188wk34Saturday12175
189wk34Saturday13175
190wk34Sunday13176
191wk34Sunday13177
192wk34Sunday13178
Tracking
Cell Formulas
RangeFormula
AK133:AK192AK133=IF(ISBLANK($A133),"",ISOWEEKNUM($A133))
AL133:AL192AL133=IF(ISBLANK($A133),"",TEXT($A133, "[$-0809]dddd"))
AM133:AM192AM133=IF(NOT(ISBLANK($A133)),COUNTIF($AG$2:$AG133,"Ongoing"),"")
AN133:AN192AN133=IF(NOT(ISBLANK($A133)),COUNTIF($AG$2:$AG133,"Closed"),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AI133:AU192Expression=$AK133<>$AK192textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Conditional formatting based on value of another cell
Replies
3
Views
257
etaf
etaf
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
190
bh24524
B
T
  • Question
Conditional Formatting for Date Comparisons
Replies
5
Views
204
etaf
etaf
P
  • Question
Applying conditional formatting to merged cells
Replies
1
Views
320
Joe4
Joe4
C
  • Solved
Formula Help (Calendar) Expanded
2
Replies
15
Views
724
Eric W
Eric W

Forum statistics

Threads
1,215,136
Messages
6,123,249
Members
449,093
Latest member
Vincent Khandagale

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top