Conditionally format the last cell in each row with valuable data

paydog23

New Member
Joined
Jul 12, 2017
Messages
25
Office Version
365
Platform
Windows
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).
 

Attachments

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,238
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,731
Office Version
365
Platform
Windows
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
BCDEF
2NoneNoneNoneNoneNone
3xxNoneNoneNone
4NoneNoneNoneNoneNone
5NoneNoneNoneNoneNone
6xNoneNoneNoneNone
7xNoneNoneNoneNone
8xxxxNone
9xNoneNoneNoneNone
10xNoneNoneNoneNone
11xNoneNoneNoneNone
12xxxxx
Sheet2 (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:F13Expression=COUNTIF(B2:$F2,"<>None")=1textNO
 

paydog23

New Member
Joined
Jul 12, 2017
Messages
25
Office Version
365
Platform
Windows
@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
BCDEF
2NoneNoneNoneNoneNone
3xxNoneNoneNone
4NoneNoneNoneNoneNone
5NoneNoneNoneNoneNone
6xNoneNoneNoneNone
7xNoneNoneNoneNone
8xxxxNone
9xNoneNoneNoneNone
10xNoneNoneNoneNone
11xNoneNoneNoneNone
12xxxxx
Sheet2 (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:F13Expression=COUNTIF(B2:$F2,"<>None")=1textNO
Thanks, but unfortunately, this code ended up formatting incorrect cells too, including all of column F.
 

paydog23

New Member
Joined
Jul 12, 2017
Messages
25
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,731
Office Version
365
Platform
Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,789
Messages
5,470,790
Members
406,723
Latest member
Fcolombo

This Week's Hot Topics

Top