Conditional Formatting Question

[doumob]

Hello

I am struggling to get the correct conditional formatting formula, or will this need VBA?

For rows 20 onwards I am trying to highlight the rows if the last column in row 14 that has text in it also has text in the corresponding row for 20 onwards.

I have tried

=AND(D$14<>"",E$14="",D20<>"",E20="")

Applying to =$20:$344

This almost works, but only highlights the cell 4 back from the last column

 

[Joe4]

Please show us some sample data of various scenarios, along with your expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[doumob]

What I get is as shown in the first screen shot, what I get is shown in the second one

I have got the following conditional formatting.

=AND(D$14<>"",E$14="",D18<>"",E18="")

Applying to =$18:$344

 

[Joe4]

Is the issue that in your example, the last column with data in row 14 is column G, but that might not always be the case?
It could be other columns too?

What about the situation where the last column with data in row 4 is column G, but then let's say that row 20 has data in column G and in column H?
Should it be highlighted because column G in both row 14 and row 20 have data,
or should it NOT be highlighted because the last column letters with data in them in rows 14 and 20 do not match?

Visually, should row 20 be highlighted or not in this example?

 

[doumob]

In my example there may be further tests added so when the next data is added H14 will be filled in then H18-H20 may be filled in.

I am trying to highlight the rows that have data in the column that is the last column where data is present for row 14.

So in the example the row should not be highlighted (but if the table is filled in correctly this wont happen so should be a moot point)

For example if the data in H is added it may look like this and then I would want just row 18 highlighted

 

[Joe4]

OK, select rows 18 down to the last row you want to apply this to, and use this Conditional Formatting formula:

=LOOKUP(2,1/($14:$14<>""),COLUMN($14:$14))=LOOKUP(2,1/(18:18<>""),COLUMN(18:18))

Result:

 

[Joe4]

If you want it so that it only highlights out to the last populated cell (and not highlight the entire row), use this variation of the formula:

=AND(LOOKUP(2,1/($14:$14<>""),COLUMN($14:$14))=LOOKUP(2,1/(18:18<>""),COLUMN(18:18)),COLUMN()<=LOOKUP(2,1/($14:$14<>""),COLUMN($14:$14)))

 

[doumob]

Thank you, that does exactly what I wanted it to do. The only issue is that it slows down the sheet too much with seconds of delay between inputting new data and it appearing on the sheet. To overcome this I have had to limit the applies to element to the first 3 columns only with the first formula. Thank you for your help, I assume that the slowness is down to the hardware that I can use rather than any software reason

 

[Joe4]

Thank you, that does exactly what I wanted it to do. The only issue is that it slows down the sheet too much with seconds of delay between inputting new data and it appearing on the sheet. To overcome this I have had to limit the applies to element to the first 3 columns only with the first formula. Thank you for your help, I assume that the slowness is down to the hardware that I can use rather than any software reason

Some of the slowness is probably because the formulas are checking the entire rows. If we can determine the maximum number of columns we could ever have, we could amend the formulas so it does not need to check every possible column in each row, and that may help with performance, i.e.
like if you were never go out past column N, we could change these references

$14:$14
$18:$18

to references like this:

$A14:$Z14
$A18:$Z18