Help pls

[SmithW1]

Im needing to make an excel sheet to record employee discount spending. Ive created a very basic sheet, but need help with making it work.

- When an employee goes over $200 I need their specific rows to highlight or otherwise indicate so. For example in the sheet below Will has a total balance of $207, in this case I would need his rows to highlight or indicate that he has gone over $200.

Thanks.

 

[kweaver]

(a) please don't post a photo. Use XL2BB to illustrate your data so that anyone helping you doesn't have to recreate it.
(b) how did you get $207 for Will when it seems 80+25+20 = 125 or 80+(2)(25)+20 = 150 ??

 

[kweaver]

Maybe you want something like this?

Sub Over200()
Dim lr, i, t As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
Range("A2:D" & lr).Interior.ColorIndex = xlNone
For i = 2 To lr
 t = WorksheetFunction.SumIf(Range("B2:B" & lr), Cells(i, "B"), Range("D2:D" & lr))
 If t >= 200 Then
 Cells(i, "B").Interior.Color = vbYellow
 Cells(i, "D").Interior.Color = vbYellow
 End If
Next i
End Sub

 

[SmithW1]

(a) please don't post a photo. Use XL2BB to illustrate your data so that anyone helping you doesn't have to recreate it.
(b) how did you get $207 for Will when it seems 80+25+20 = 125 or 80+(2)(25)+20 = 150 ??

My bad I had taken the screen shot before I had updated the values.

Book1
	A	B	C	D	E
1	Date	Team member name	Items	Total amount (Before discount)	Authorising Manager
2	3/06/2023	jacob	socks x1	11	matt
3	5/06/2023	will	boots	87	jackson
4	8/06/2023	sam	pants x1	15	matt
5	11/06/2023	william	underwear pack x1	12	dan
6	15/06/2023	will	pants x2	65	dan
7	16/06/2023	jake	shirt x1 & pants x1	27	dan
8	20/06/2023	will	pants x1	55	jackson
9	23/06/2023	william	socks x3 & shorts x1	40	matt
10	28/06/2023	jake	pants x2	45	dan
Sheet1

 

[Peter_SSs]

You could try this conditional formatting

23 07 12.xlsm
	A	B	C	D	E
1	Date	Team member name	Items	Total amount (Before discount)	Authorising Manager
2	3/06/2023	jacob	socks x1	11	matt
3	5/06/2023	will	boots	87	jackson
4	8/06/2023	sam	pants x1	15	matt
5	11/06/2023	william	underwear pack x1	12	dan
6	15/06/2023	will	pants x2	65	dan
7	16/06/2023	jake	shirt x1 & pants x1	27	dan
8	20/06/2023	will	pants x1	55	jackson
9	23/06/2023	william	socks x3 & shorts x1	40	matt
10	28/06/2023	jake	pants x2	45	dan
CF > 200

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E10	Expression	=SUMIFS($D$2:$D$10,$B$2:$B$10,$B2)>200	text	NO

 

[SmithW1]

You could try this conditional formatting

23 07 12.xlsm
	A	B	C	D	E
1	Date	Team member name	Items	Total amount (Before discount)	Authorising Manager
2	3/06/2023	jacob	socks x1	11	matt
3	5/06/2023	will	boots	87	jackson
4	8/06/2023	sam	pants x1	15	matt
5	11/06/2023	william	underwear pack x1	12	dan
6	15/06/2023	will	pants x2	65	dan
7	16/06/2023	jake	shirt x1 & pants x1	27	dan
8	20/06/2023	will	pants x1	55	jackson
9	23/06/2023	william	socks x3 & shorts x1	40	matt
10	28/06/2023	jake	pants x2	45	dan
CF > 200

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E10	Expression	=SUMIFS($D$2:$D$10,$B$2:$B$10,$B2)>200	text	NO

worked perfectly, thank you.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.