conditional formatting based on adjacent cells matching

J

jbrown021286

Board Regular
Joined
Mar 13, 2023
Messages
81
Office Version
  1. 365
Platform
  1. Windows
hours log.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
16/1/2024
2DaydatenotesColumn2ROstatusSUMDaignosisCODEJOBFLAG TIMEADJUSTMENTColumn1R.O.Hours
3Wednesday5/29/2024 6211111g14.8motor  14.8don’t countWaitingincorect 621111114.8
4Saturday6/1/2024 6211224g1.0  1000Check List62112241
5Wednesday5/29/2024 6211224g22.7motor  22.7Todays totalBalance non verifyedBalance verifyed89.5621122422.7
6Saturday6/1/2024 6212069g0.3  0.35.310.1-4.2621122422.7
7Friday5/31/2024 6212490g20.0  20TotalNot VerifyedVerifyed62120690.3
8Wednesday5/29/2024 6212778g1.0motordDiag181.114.366.8621249020
9Friday5/31/2024 6212919g0.1afair filter0.162127781
10Friday5/31/2024 6212919g0.2cfcabin filter0.262129190.1
11Friday5/31/2024 6212919g1.0AAlignment162129190.2
12Friday5/31/2024 6212919g1.24t4 tires1.262129191
13Friday5/31/2024 6212919g1.5rear stud  1.562129191.2
14Friday5/31/2024 6212919g2.0front stud  262129191.5
15Friday5/31/2024 6213016g1.0pressure switch  162129192
16Wednesday5/29/2024X62112247.0.5 head gasket  762130161
17Saturday6/1/2024X62124651.0CSCoolant Service1DayHoursAverage
18Friday5/24/2024X62124651.8odyssey fandDiag10.8xMonday037.9
19Friday5/31/2024X62129411.5drilled front stud  1.5xTuesday0
20Saturday6/1/2024X62129412.0  2xWednesday45.5
21Saturday6/1/2024X62131071.0  1xThursday0
22      xFriday30.3
23      xSaturday0
24      
25      Today 5.3
x
Cell Formulas
RangeFormula
A1A1=TODAY()
B3:B25B3=IFERROR(TEXT(WEEKDAY($C3),"Dddd"),"")
C3:C25C3=IF([@RO]<>"",IF(C3="",TODAY(),C3),"")
K3:K25K3=IF(J3="","",INDEX($AA$3:$AA$91,MATCH(J3,$Z$3:$Z$91,0),1))
L3:L25L3=IF(K3="","",INDEX($AB$3:$AB$91,MATCH(J3,$Z$3:$Z$91,0),1))
P4P4=SUMIF(G:G,"N",H:H)+SUMIF(G:G,"cnf",H:H)
Q4Q4=SUMIF(G:G,"w",H:H)
R4R4=SUMIF(G:G,"x",H:H)
U5U5=SUM(X:X)
P6P6=SUMIF($C$3:$C$280,A1,$H3:$H280)
Q6Q6=P8-AV4
R6R6=R8-AV4
P8P8=SUM(H:H,)-SUMIF(G:G,"N",H:H)-SUMIF(G:G,"nf",H:H)-Q11
Q8Q8=SUMIF(G:G,"",H:H)+SUMIF(G:G,"c",H:H)
R8R8=SUMIF(G:G,"G",H:H)-Q11
R18R18=IFERROR(AVERAGEIFS(Q18:Q25,Q18:Q25,">0",O18:O25,"X"),0)
Q18:Q23Q18=SUMIFS($H$3:$H$280,$B$3:$B$280,P18,$C$3:$C$280,"<"&A$1)
E3:E25E3=IF( OR(AND( F3="",G3=""),AND(F3<>"",G3="G")), "", IF( AND(F3<>"", G3=""), "X", "Criteria NOT met"))
H3:H25H3=SUMIF(Table111293345[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table111293345[@[FLAG TIME]:[ADJUSTMENT]])
Q25Q25=SUMIF($C$3:$C$280,A1,$H3:$H280)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R6Cell Value>-0.1textNO
R6Cell Value>-0.1textNO
R6Cell Value<0textNO
Q6Cell Value>-0.1textNO
Q6Cell Value>-0.1textNO
Q6Cell Value<0textNO
M22:M52,F91:F280,F3:F88Expression=COUNTIF(W:W,F3)>0textNO
E91:E280,E3:E88Expression=(E3="x")*(C3=$A$1)textNO
E91:E280,E3:E88Expression=(E3="")*(F3<>"")textNO
E91:E280,E3:E88Expression=(E3="x")*(F3<>"")textNO
G91:G280,I5:I8,G3:G88Cell Value=$P$42textNO
I19Cell Value=$P$41textNO
I19Cell Value=$P$40textNO
I19Cell Value=$P$39textNO
I19Cell Value=$P$38textNO
I19Cell Value=$P$37textNO
I19Cell Value=$P$36textNO
G91:G280,I5:I8,G3:G88Cell Value=$P$41textNO
G91:H280,G3:H4,G10:H88,H5:I8,H9,G5:G9Cell Value=$P$40textNO
G91:H280,G3:H4,G10:H88,H5:I8,H9,G5:G9Cell Value=$P$39textNO
G91:H280,G3:H4,G10:H88,H5:I8,H9,G5:G9Cell Value=$P$38textNO
G91:H280,G3:H4,G10:H88,H5:I8,H9,G5:G9Cell Value=$P$37textNO
G91:H280,G3:H4,G10:H88,H5:I8,H9,G5:G9Cell Value=$P$36textNO
W3:W280Expression=COUNTIF(F:F,W3)>0textNO
H3:H88,H91:H280Expression=" =COUNTIFS(D:D,A1,E:E,J2)>0"textNO
X3:X280Expression=COUNTIFS(F:F,W3,H:H,X3)>0textNO
H3:H88,H91:H280Expression=COUNTIFS(W:W,F3,X:X,H3)>0textNO
M3:O5,N6:O7Expression=COUNTIFS(XFA:XFA,L3,XFC:XFC,M3)>0textNO


i am wanting to have a conational formatting formula that when applied to a cell in a row it highlights it if the adjacent cell in column W has a value in it and if the values in column W and X do not match the values in F and H. in the example i have here you can see everything would match till i hit row 6. after that i would need the formula to highlight the cells i put it on down to where column W is empty at row 17. any help would be appreciated
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
column W and X do not match the values in F and H.
Click to expand...
=AND( $W3<>$F3,$X3<>$H3)

not sure what columns you want to highlight

Book2
ABCDEFGHIJKLMNOPQRSTUVWX
16/1/24
2DaydatenotesColumn2ROstatusSUMDaignosisCODEJOBFLAG TIMEADJUSTMENTColumn1R.O.Hours
3 =IF([@RO]<>"",IF(C3="",TODAY(),C3),"") 6211111g14.800000000motor  14.8don’t countWaitingincorect 621111114.8
4 =IF([@RO]<>"",IF(C4="",TODAY(),C4),"") 6211224g1  1000Check List62112241
5 =IF([@RO]<>"",IF(C5="",TODAY(),C5),"") 6211224g22.7motor  22.7Todays totalBalance non verifyedBalance verifyed89.5621122422.7
6 =IF([@RO]<>"",IF(C6="",TODAY(),C6),"") 6212069g1  0.3060.459.4621122422.7
7 =IF([@RO]<>"",IF(C7="",TODAY(),C7),"") 6212490g2  20TotalNot VerifyedVerifyed62120690.3
8 =IF([@RO]<>"",IF(C8="",TODAY(),C8),"") 6212778g3motord#N/A#N/A60.4159.4621249020
9 =IF([@RO]<>"",IF(C9="",TODAY(),C9),"") 6212919g4af#N/A#N/A62127781
10 =IF([@RO]<>"",IF(C10="",TODAY(),C10),"") 6212919g5cf#N/A#N/A62129190.1
11 =IF([@RO]<>"",IF(C11="",TODAY(),C11),"") 6212919g0.2A#N/A#N/A62129190.2
12 =IF([@RO]<>"",IF(C12="",TODAY(),C12),"") 6212919g14t#N/A#N/A62129191
13 =IF([@RO]<>"",IF(C13="",TODAY(),C13),"") 6212919g1.2rear stud  1.562129191.2
14 =IF([@RO]<>"",IF(C14="",TODAY(),C14),"") 6212919g1.5front stud  262129191.5
15 =IF([@RO]<>"",IF(C15="",TODAY(),C15),"") 6213016g2pressure switch  162129192
16 =IF([@RO]<>"",IF(C16="",TODAY(),C16),"")X62112241.5 head gasket  762130161
17 =IF([@RO]<>"",IF(C17="",TODAY(),C17),"")X6212465=SUMIF(Table111293345[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table111293345[@[FLAG TIME]:[ADJUSTMENT]])CS#N/A#N/ADayHoursAverage
18 =IF([@RO]<>"",IF(C18="",TODAY(),C18),"")X6212465=SUMIF(Table111293345[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table111293345[@[FLAG TIME]:[ADJUSTMENT]])odyssey fand#N/A#N/A0.8xMonday00
19 =IF([@RO]<>"",IF(C19="",TODAY(),C19),"")X6212941=SUMIF(Table111293345[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table111293345[@[FLAG TIME]:[ADJUSTMENT]])drilled front stud  1.5xTuesday0
20 =IF([@RO]<>"",IF(C20="",TODAY(),C20),"")X6212941=SUMIF(Table111293345[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table111293345[@[FLAG TIME]:[ADJUSTMENT]])  2xWednesday0
21 =IF([@RO]<>"",IF(C21="",TODAY(),C21),"")X6213107=SUMIF(Table111293345[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table111293345[@[FLAG TIME]:[ADJUSTMENT]])  1xThursday0
22 =IF([@RO]<>"",IF(C22="",TODAY(),C22),"") =SUMIF(Table111293345[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table111293345[@[FLAG TIME]:[ADJUSTMENT]])  xFriday0
23 =IF([@RO]<>"",IF(C23="",TODAY(),C23),"") =SUMIF(Table111293345[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table111293345[@[FLAG TIME]:[ADJUSTMENT]])  xSaturday0
Sheet1
Cell Formulas
RangeFormula
A1A1=TODAY()
K3:K23K3=IF(J3="","",INDEX($AA$3:$AA$91,MATCH(J3,$Z$3:$Z$91,0),1))
L3:L23L3=IF(K3="","",INDEX($AB$3:$AB$91,MATCH(J3,$Z$3:$Z$91,0),1))
P4P4=SUMIF(G:G,"N",H:H)+SUMIF(G:G,"cnf",H:H)
Q4Q4=SUMIF(G:G,"w",H:H)
R4R4=SUMIF(G:G,"x",H:H)
U5U5=SUM(X:X)
P6P6=SUMIF($C$3:$C$280,A1,$H3:$H280)
Q6Q6=P8-AV4
R6R6=R8-AV4
P8P8=SUM(H:H,)-SUMIF(G:G,"N",H:H)-SUMIF(G:G,"nf",H:H)-Q11
Q8Q8=SUMIF(G:G,"",H:H)+SUMIF(G:G,"c",H:H)
R8R8=SUMIF(G:G,"G",H:H)-Q11
R18R18=IFERROR(AVERAGEIFS(Q18:Q25,Q18:Q25,">0",O18:O25,"X"),0)
B3:B23B3=IFERROR(TEXT(WEEKDAY($C3),"Dddd"),"")
E3:E23E3=IF( OR(AND( F3="",G3=""),AND(F3<>"",G3="G")), "", IF( AND(F3<>"", G3=""), "X", "Criteria NOT met"))
Q18:Q23Q18=SUMIFS($H$3:$H$280,$B$3:$B$280,P18,$C$3:$C$280,"<"&A$1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E3:H25Expression=AND( $W3<>$F3,$X3<>$H3)textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
253
Joe4
Joe4
J
  • Question Question
copying adjacent cells based on value of referenced cell
Replies
4
Views
256
arun_eagertolearn
A
J
  • Solved
returning a value if multiple criteria are true
Replies
3
Views
394
etaf
etaf
J
  • Question Question
sumif function with delayed application
Replies
5
Views
317
jbrown021286
J
J
  • Solved
conditional formatting with multiple criteria
Replies
8
Views
387
jbrown021286
J

Forum statistics

Threads
1,223,880
Messages
6,175,157
Members
452,615
Latest member
bogeys2birdies

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