Conditional formating Question

[sujirpinna]

Hello - I want to apply a rule , that will set a top border and a bottom border for the 1st instance of date and the last instance of the same date respectively .

Pls refer to the before image (without borders) and after image (with border applied to the top most and the bottom most row for each date).

My date will always be sorted in descending order of date .

Pls help with this conditional formatting of applying borders as i do not have a solution.

Thank you for time and interest in solving my problem.

 

[dreid1011]

Select your data range and try these CF rules, and change the ranges to suit your data:

Book1
	A
1	11/29/2023
2	11/29/2023
3	11/29/2023
4	11/22/2023
5	11/15/2023
6	11/15/2023
7	11/8/2023
8	11/8/2023
9	11/8/2023
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A9	Expression	=COUNTIF($A$1:$A1,A1)=COUNTIF($A$1:$A$1000,A1)	text	NO
A1:A9	Expression	=COUNTIF($A$1:$A1,A1)=1	text	NO

 

[sujirpinna]

Hello ,
Thanks for your quick response . This bordering logic should be applied to the entire table , based on the dates in 4th column . Here is what i observed and need your further help please .

1) When i applying this formatting only for the 4th column it is giving the result that i want . (one small thing is that the Top border for the First row is not appearing. ie 29th November is only having a bottom border)

2) When i select the entire table for the "Applies To" section of conditional formating (A15:E33) then i am getting a different result . The 4th column is accurate , but for all other columns Top and Bottom borders are being applied to each and every line.

I hope i am clear in conveying . kindly check the attached screenshot and hopefully it is a quick fix.

 

[Alex Blakenburg]

Ideally please provide and XL2BB sample of your data so whoever helps you doesn't manually have to create sample data to work on.
It also gives the row, column and sheet references used in your data which means you have to spend less time interpreting and re-engineering the solution to fit your data.
If you have to give a picture please include the row and column information in the picture.

The main change to @dreid1011 solution would be to place a "$" in front of the A1 used in the criteria.
Assuming row 14 has the table headings in it, here is dreid's solution customised.

20231202 Conditional Formatting Borders sujirpinna.xlsx
	A	B	C	D	E
14	Col1	Col2	Col3	Col4	Col5
15				29/11/2023
16				29/11/2023
17				29/11/2023
18				29/11/2023
19				30/11/2023
20				1/12/2023
21				1/12/2023
22				1/12/2023
23				1/12/2023
24				3/12/2023
25				3/12/2023
26				3/12/2023
27				10/12/2023
28				15/12/2023
29				15/12/2023
30				15/12/2023
31				15/12/2023
32				15/12/2023
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A15:E32	Expression	=COUNTIF($D$15:$D15,$D15)=1	text	NO
A15:E32	Expression	=COUNTIF($D$15:$D15,$D15)=COUNTIF($D$15:$D$33,$D15)	text	NO

 

[kevin9999]

Another option?

Book1
	A	B	C	D	E	F	G
1	HDR1	HDR2	HDR3	HDR4	HDR5	HDR6	HDR7
2					2-Dec-23
3					2-Dec-23
4					2-Dec-23
5					3-Dec-23
6					4-Dec-23
7					4-Dec-23
8					4-Dec-23
9					4-Dec-23
10					5-Dec-23
11					6-Dec-23
12
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:G11	Expression	=$E2<>$E3	text	YES
A2:G11	Expression	=$E2<>$E1	text	YES