VBA conditional order issue

[tezza]

Hi all,

I've got a list of conditional formats that need to go in a specific order (see below), no matter how I do it I can't get them right.

Please can someone help?

Office 365

Private Sub condformat()
'
' Macro6 Macro
'

'
    Worksheets("RC_Call_Logs").Select
    
    For Each TmpSht In ThisWorkbook.Sheets
    TmpSht.Cells.FormatConditions.Delete
    Next
    
    Columns("I:I").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$M1=""No Start Time"""
    'Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
   
    Columns("J:J").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I2>J2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    
    Columns("K:K").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTA($A1:$M1)=1"
    'Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).StopIfTrue = True
            
    Columns("K:K").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$H1-$K1>TIME(0,15,0)"
    'Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399945066682943
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    

End Sub

 

[kevin9999]

no matter how I do it I can't get them right.

It would probably help if you could describe what "right" looks like.
Could you share your file via Dropbox, Google Drive or similar file sharing platform (you can easily disguise any sensitive data) and describe in words exactly how you want the order of the conditional formatting to work?

 

[tezza]

It would probably help if you could describe what "right" looks like.
Could you share your file via Dropbox, Google Drive or similar file sharing platform (you can easily disguise any sensitive data) and describe in words exactly how you want the order of the conditional formatting to work?

Here's a visual of the original post and the order I need it to be:

 

[kevin9999]

Thank you for that. If you're unable to share your actual file, could you at least provide a copy of your sheet via the XL2BB - Excel Range to BBCodeXL2BB - Excel Range to BBCode - without which it would be impossible to test whether any suggested code will give you what you want.

 

[tezza]

Thank you for that. If you're unable to share your actual file, could you at least provide a copy of your sheet via the XL2BB - Excel Range to BBCodeXL2BB - Excel Range to BBCode - without which it would be impossible to test whether any suggested code will give you what you want.

As you see, the 'Stop if True' format is at the bottom, but I need it in third position.

All it does is remove the orange box in Col K just above each grey line but for work purpose it helps alot.

_RCLogs_tester.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Day of Week	Date of Call	Staff	Clients	Chargeable Rate Sheet	Call Start Time	Call End Time	Call Duration	Real Start	Real End	Duration	CCF	Notes		1	158
2	Monday	22/05/2023	ED	Bloggs, Joe	Customer	11:00	14:00	03:00	10:58	13:52	02:54	123abc	No Start Time		02:54
3	Wednesday	24/05/2023	ED	Bloggs, Joe	Customer	11:00	17:00	06:00	10:56	16:56	06:00	123abc	No Start Time		06:00
4	Friday	26/05/2023	SC	Bloggs, Joe	Customer	10:30	13:30	03:00	10:31	13:24	02:53	123abc	No Start Time		02:53
5	Monday	29/05/2023	ED	Bloggs, Joe	Customer	11:00	14:00	03:00	10:53	13:52	02:59	123abc	No Start Time		02:59
6	Wednesday	31/05/2023	ED	Bloggs, Joe	Customer	11:00	17:00	06:00	10:52	16:53	06:01	123abc	No Start Time		06:01
7	Friday	02/06/2023	SC	Bloggs, Joe	Customer	10:30	13:30	03:00	10:29	13:29	03:00	123abc	No Start Time		03:00
8	Monday	05/06/2023	ED	Bloggs, Joe	Customer	11:00	14:00	03:00	10:59	13:54	02:55	123abc	No Start Time		02:55
9	Wednesday	07/06/2023	ED	Bloggs, Joe	Customer	11:00	17:00	06:00	11:07	17:05	05:58	123abc	No Start Time		05:58
10	Friday	09/06/2023	SC	Bloggs, Joe	Customer	10:30	13:30	03:00	10:25	13:26	03:01	123abc	No Start Time		03:01
11	Tuesday	13/06/2023	JG	Bloggs, Joe	Customer	10:00	13:00	03:00	10:01	13:01	03:00	123abc	No Start Time		03:00
12	Wednesday	14/06/2023	ED	Bloggs, Joe	Customer	11:00	17:00	06:00	10:53	16:46	05:53	123abc	No Start Time		05:53
13	Friday	16/06/2023	SC	Bloggs, Joe	Customer	10:30	13:30	03:00	10:37	13:34	02:57	123abc	No Start Time		02:57
14								48:00						P	00:00
15															00:00
16															00:00
Call_Logs

Cell Formulas
Range	Formula
O1	O1	=COUNTIF(N2:N10000,"P")
P1	P1	=COUNTA(UNIQUE(D2:D10000))-1
N2:N16	N2	=IF(AND(COUNT(A2:M2)=1,H2<>""),"P","")
O2:O16	O2	=J2-I2
P2:P16	P2	=IF(OR(O2=K2,MROUND(O2,"00:01")=MROUND(K2,"00:01")),"","Check")
H14	H14	=SUM(H2:H13)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J:J	Expression	=I2>J2	text	NO
I:I	Expression	=$M1="No Start Time"	text	NO
K:K	Expression	=COUNTA($A1:$M1)=1	text	NO
K:K	Expression	=$H1-$K1>TIME(0,15,0)	text	YES

 

[tezza]

I've figured it out using an AND function in the conditional format:

=AND(COUNTA(A1:M1)>1,$H1-$K1>TIME(0,15,0))

I just had to learn about how to put formulas in the conditional format properly.

 

[kevin9999]

I've figured it out using an AND function in the conditional format:

=AND(COUNTA(A1:M1)>1,$H1-$K1>TIME(0,15,0))

I just had to learn about how to put formulas in the conditional format properly.

Well done