Formula help

[MESTeacher]

I have a worksheet with student's weekly progress monitoring scores. I have set up conditional formatting for blank cells to remain unformatted and for the cells to be compared to the goal score and turn blue if equal to or greater than. I want to set up conditional formatting where for example this weeks score will be compared to last weeks score and will be green if greater, yellow if equal and red if lower than. I know I can do this with this formula =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1) My problem is blank cells. If a student is absent, I want it to skip the blank cell(s) and compare it to the last non blank cell to the left. I have uploaded an example of what I have so far.

Sample Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Progress Monitoring Scores 2024-25				Aug-12	Aug-19	Aug-26	Sep-2	Sep-9	Sep-16	Sep-23	Sep-30	Oct-7	Oct-14	Oct-21	Oct-28	Nov-4	Nov-11	Nov-18	Nov-25	Dec-2	Dec-9	Dec-16
2	Grade	Student	PM Test	Goal	Wk 1	Wk 2	Wk 3	Wk 4	Wk 5	Wk 6	Wk 7	Wk 8	Wk 9	Wk 10	Wk 11	Wk 12	Wk 13	Wk 14	Wk 15	Wk 16	Wk 17	Wk 18	Wk 19
3	1	Student 1	MFF-1D	14	7	7	10	8	13	11	11	11	8		8	17		11	17
4			NCF-P	29	8	9	11	14	12	17	17	17	14		20	13		18	20
5	1	Student 2	MFF-1D	14	7	3	9	6	9	9	11	10	11		11	12			11
6			NCF-P	29	8	9	15	17	20	17	21	24	21		22	22			23
7	1	Student 3	MFF-1D	14	3	4	4	3	7	6	8	6	9		10	10		13	10
8			NCF-P	28	7	8	8	9	7	9	13	13	11		15	14		15	18
9	1	Student 4	MFF-1D	14	7	4	7	7	10	7		8	9			7		10	12
10			NCF-P	29	17	22	22	23	26	25		25	25			26		27	27
11	1	Student 5	MFF-1D	13				3	1	6	6	7			7	5		4	4
12			NCF-P	31				11	17	17	16	18			16	22		21	22
PM

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:W14	Cell	contains a blank value	text	YES
E3:W14	Cell Value	>=$D3	text	YES

 

[Anthony47]

I suggest you do that via macro. For example:

Sub CkProgr()
Dim wArea As Range, I As Long, J As Long
Dim Prev As Long
'
Set wArea = Range("A1").CurrentRegion.Offset(2, 4)
For I = 1 To wArea.Rows.Count - 2
    Prev = 0
    For J = 1 To wArea.Columns.Count - 4
        wArea.Cells(I, J).Interior.ColorIndex = xlNone
        If wArea.Cells(I, J).Value <> "" And Prev > 0 Then
            If wArea.Cells(I, J).Value > Prev Then
                wArea.Cells(I, J).Interior.Color = RGB(100, 255, 100)
            ElseIf wArea.Cells(I, J).Value < Prev Then
                wArea.Cells(I, J).Interior.Color = RGB(255, 100, 100)
            ElseIf wArea.Cells(I, J).Value = Prev Then
                wArea.Cells(I, J).Interior.Color = RGB(255, 255, 100)
            End If
        End If
        If wArea.Cells(I, J).Value <> "" Then Prev = wArea.Cells(I, J).Value
    Next J
Next I
End Sub

Copy this code into a Standard Module of your vba project; then run Sub CkProgr when you wish to mark the situation; from Excel, press Alt-F8, select CkProgr from the list of available macros, press the Run button.

You can use the following similar macro to check against the Goal:

Sub CkGoal()
Dim wArea As Range, I As Long, J As Long
Dim Prev As Long
'
Set wArea = Range("A1").CurrentRegion.Offset(2, 4)
For I = 1 To wArea.Rows.Count - 2
    Prev = wArea.Cells(I, 0).Value
    For J = 1 To wArea.Columns.Count - 4
        wArea.Cells(I, J).Interior.ColorIndex = xlNone
        If wArea.Cells(I, J).Value >= Prev Then
                wArea.Cells(I, J).Interior.Color = RGB(100, 100, 255)
        End If
    Next J
Next I
End Sub

Then run Sub CkGoal when you wish to check for the situation

 

[Peter_SSs]

Welcome to the MrExcel board!

I want to set up conditional formatting where for example this weeks score will be compared to last weeks score and will be green if greater, yellow if equal and red if lower than. ... My problem is blank cells. If a student is absent, I want it to skip the blank cell(s) and compare it to the last non blank cell to the left.

That should be fine with standard Conditional Formatting. Since there is nothing to compare to for Wk 1 my CF starts at Wk 2
Where I have 9^9 in my formulas, that just needs to be any number that will be greater than any possible number in the range. So, you can leave it as it is or perhaps 100 might be big enough?
What I wasn't sure about was if this had to fit in with the blue and if so how?
For example, Cell P3 is above the goal so perhaps blue?, but it is also greater than O3 so perhaps green?
So my suggestion is just for the green/yellow/red options. Clarification required if the blue is to be included.

24 11 22.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	Wk 1	Wk 2	Wk 3	Wk 4	Wk 5	Wk 6	Wk 7	Wk 8	Wk 9	Wk 10	Wk 11	Wk 12	Wk 13	Wk 14	Wk 15	Wk 16	Wk 17	Wk 18	Wk 19
3	7	7	10	8	13	11	11	11	8		8	17		11	17
4	8	9	11	14	12	17	17	17	14		20	13		18	20
5	7	3	9	6	9	9	11	10	11		11	12			11
6	8	9	15	17	20	17	21	24	21		22	22			23
7	3	4	4	3	7	6	8	6	9		10	10		13	10
8	7	8	8	9	7	9	13	13	11		15	14		15	18
9	7	4	7	7	10	7		8	9			7		10	12
10	17	22	22	23	26	25		25	25			26		27	27
11				3	1	6	6	7			7	5		4	4
12				11	17	17	16	18			16	22		21	22
Compare with last

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:W12	Expression	=AND(F3<>"",COUNT($E3:E3),F3<LOOKUP(9^9,$E3:E3))	text	NO
F3:W12	Expression	=AND(F3<>"",COUNT($E3:E3),F3=LOOKUP(9^9,$E3:E3))	text	NO
F3:W12	Expression	=AND(F3<>"",COUNT($E3:E3),F3>LOOKUP(9^9,$E3:E3))	text	NO

Edit:
If you did want to apply the CF to the entire range, including Wk1, for consistency then ..

24 11 22.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	Goal	Wk 1	Wk 2	Wk 3	Wk 4	Wk 5	Wk 6	Wk 7	Wk 8	Wk 9	Wk 10	Wk 11	Wk 12	Wk 13	Wk 14	Wk 15	Wk 16	Wk 17	Wk 18	Wk 19
3	14	7	7	10	8	13	11	11	11	8		8	17		11	17
4	29	8	9	11	14	12	17	17	17	14		20	13		18	20
5	14	7	3	9	6	9	9	11	10	11		11	12			11
6	29	8	9	15	17	20	17	21	24	21		22	22			23
7	14	3	4	4	3	7	6	8	6	9		10	10		13	10
8	28	7	8	8	9	7	9	13	13	11		15	14		15	18
9	14	7	4	7	7	10	7		8	9			7		10	12
10	29	17	22	22	23	26	25		25	25			26		27	27
11	13				3	1	6	6	7			7	5		4	4
12	31				11	17	17	16	18			16	22		21	22
Compare with last (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:W12	Expression	=AND(E3<>"",COUNT($E3:E3)>1,E3<LOOKUP(9^9,$D3:D3))	text	NO
E3:W12	Expression	=AND(E3<>"",COUNT($E3:E3)>1,E3=LOOKUP(9^9,$D3:D3))	text	NO
E3:W12	Expression	=AND(E3<>"",COUNT($E3:E3)>1,E3>LOOKUP(9^9,$D3:D3))	text	NO