Code To Highlight Rows When Out Of Sequence

[Dazzawm]

I need a code please that will highlight rows when not in order. The code needs to look at column C then see all the rows that match. In the example below the first 3 rows match so then needs to look at column AF. Because Water Pump 2 is above Water Pump 1 then the rows need to be highlighted as they are. The next set of 3 are left clear because Water Pump 1 is above Water Pump 2 so that is ok, the bottom 2 are highlighted because Water Pump 2 is above Water Pump 1 then the rows need to be highlighted as they are and so on with the rest of the rows in the file. I have hidden the rest of the columns for clarity. A sort can't be done as there is many columns with data in. There may be 2 rows that match in C or there may be 10 rows that match. Thanks.

VLAST16 7001																												Data1	(Sell First Where Possible)
VLAST16 7001																												Data2	Water Pump 2
VLAST16 7001																												Data3	Water Pump 1
VLAST16 7004																												Data4	(Sell First Where Possible)
VLAST16 7004																												Data5	Water Pump 1
VLAST16 7004																												Data6	Water Pump 2
VLAST16 5022																												Data4	Water Pump 2
VLAST16 5022																												Data5	Water Pump 1

 

[JamesCanale]

I used conditional formatting for this:

MrExcelPlayground19.xlsx
	C																												AE	AF
2	VLAST16 7001																												Data1	(Sell First Where Possible)
3	VLAST16 7001																												Data2	Water Pump 2
4	VLAST16 7001																												Data3	Water Pump 1
5	VLAST16 7004																												Data4	(Sell First Where Possible)
6	VLAST16 7004																												Data5	Water Pump 1
7	VLAST16 7004																												Data6	Water Pump 2
8	VLAST16 5022																												Data4	Water Pump 2
9	VLAST16 5022																												Data5	Water Pump 1
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:AF9	Expression	=LET(a,FILTER($C$2:$AF$9,$C$2:$C$9=$C2),b,TAKE(a,,-1),d,FIND("/", SUBSTITUTE(b," ","/", LEN(b)-LEN(SUBSTITUTE(b," ","")))),e,VALUE(MID(b,d,99)),f,FILTER(e,NOT(ISERR(e))),g,VSTACK(0,DROP(f,-1)),h,AND(f-g>0),NOT(h))	text	NO

 

[Dazzawm]

I used conditional formatting for this:

MrExcelPlayground19.xlsx
	C																												AE	AF
2	VLAST16 7001																												Data1	(Sell First Where Possible)
3	VLAST16 7001																												Data2	Water Pump 2
4	VLAST16 7001																												Data3	Water Pump 1
5	VLAST16 7004																												Data4	(Sell First Where Possible)
6	VLAST16 7004																												Data5	Water Pump 1
7	VLAST16 7004																												Data6	Water Pump 2
8	VLAST16 5022																												Data4	Water Pump 2
9	VLAST16 5022																												Data5	Water Pump 1
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:AF9	Expression	=LET(a,FILTER($C$2:$AF$9,$C$2:$C$9=$C2),b,TAKE(a,,-1),d,FIND("/", SUBSTITUTE(b," ","/", LEN(b)-LEN(SUBSTITUTE(b," ","")))),e,VALUE(MID(b,d,99)),f,FILTER(e,NOT(ISERR(e))),g,VSTACK(0,DROP(f,-1)),h,AND(f-g>0),NOT(h))	text	NO

Thanks but I need a macro please as its to be used on many files (personal macro workbook) and much data on each so formulas are no good.

 

[Dazzawm]

Anyone any ideas on a macro please?

 

[LuKate]

I assume Row 1 is a header row and data starts in A2. Is that correct?

What cells do you want highlighted, the entire table row or just cells in columns C and AF?