Conditionally format the last cell in each row with valuable data

[paydog23]

I have a dataset for wells and 5 treatment plants (TP1, TP2, TP3, TP4, and TP5), which extends from A1 to F841. The flow path for each well is such that the well flows to TP1, then TP2, and so on. However, sometimes the flow path does not extend all the way to TP5. I have manually entered "None" in the cells corresponding to the non-existent TP2s, TP3s, TP4s, and TP5s. However, I would like to conditionally format or automatically color the last cell in each row that does have a TP PS Code (i.e. 0110010-020, instead of none). I have uploaded 2 pictures. The first picture shows the dataset I have and the second picture shows my desired output (I have manually filled in the cells yellow).

 

[DRSteele]

I don't feel like typing in that data, so all I can help you with is a video about conditional formatting that might benefit you in your efforts.
ExcelIsFun_CF

 

[Peter_SSs]

I don't feel like typing in that data,

@paydog23
I agree with DRSteele. Suggest you look at the links in my signature block below for a good way to provide sample copyable data.

Try selecting B2:F841 and apply the Conditional Formatting shown below.

Book1
	B	C	D	E	F
2	None	None	None	None	None
3	x	x	None	None	None
4	None	None	None	None	None
5	None	None	None	None	None
6	x	None	None	None	None
7	x	None	None	None	None
8	x	x	x	x	None
9	x	None	None	None	None
10	x	None	None	None	None
11	x	None	None	None	None
12	x	x	x	x	x
Sheet2 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:F13	Expression	=COUNTIF(B2:$F2,"<>None")=1	text	NO

 

[paydog23]

@paydog23
I agree with DRSteele. Suggest you look at the links in my signature block below for a good way to provide sample copyable data.

Try selecting B2:F841 and apply the Conditional Formatting shown below.

Book1
	B	C	D	E	F
2	None	None	None	None	None
3	x	x	None	None	None
4	None	None	None	None	None
5	None	None	None	None	None
6	x	None	None	None	None
7	x	None	None	None	None
8	x	x	x	x	None
9	x	None	None	None	None
10	x	None	None	None	None
11	x	None	None	None	None
12	x	x	x	x	x
Sheet2 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:F13	Expression	=COUNTIF(B2:$F2,"<>None")=1	text	NO

Thanks, but unfortunately, this code ended up formatting incorrect cells too, including all of column F.

 

[paydog23]

Thanks, but unfortunately, this code ended up formatting incorrect cells too, including all of column F.

Actually, I found out what the issue was--I extended the countif range to column A, and then it formatted correctly.

 

[Peter_SSs]

Actually, I found out what the issue was--I extended the countif range to column A, and then it formatted correctly.

If you have it working that is fine, but I think the actual issue was not with the CF formula range but you didn't apply it as suggested:

Try selecting B2:F841 and apply the Conditional Formatting shown below.

If you selected A2:F841 then the CF formula I suggested would act incorrectly just as you described.